I need to do a mass update to a column value. If there are issues I'll nee开发者_如何学Cd to be able to revert the column back to it's previous value. So I'm backup the values to a temporary table.
BTW, I'm using Oracle.
CREATE TABLE tmp_trial_date_backup AS SELECT cust_id, trial_days FROM customer
WHERE (trial_days = 0 or trial_days = -99)
Subsequently I'll perform the update.
UPDATE customer SET trial_days = 1 WHERE (trial_days = 0 or trial_days = -99)
My quest is... How can I restore the column values from the tmp table I've created ?do
In the end I did the following.
UPDATE customer c SET trial_days = (SELECT trial_days from tmp_trial_days_backup
where tmp_trial_days_backup.cust_id = customer_bond.cust_id)
WHERE EXISTS (SELECT 1 from tmp_trial_days_backup tmp WHERE tmp.cust_id = c.cust_id)
Following update resets the trial_days from customer
on a SQL Server database.
UPDATE c
SET trial_days = tmp.trial_days
FROM customer c
INNER JOIN tmp_trial_date_backup tmp ON tmp.cust_id = c.cust_id
Perhaps easier might have been to add a column trial_days_backup
to the customer
table.
精彩评论