Updating Multiple Rows with multiple values

[aalesandro]

My DB employs a change log table which records ANY changes made to the database whether through the user interface or through SQL itself. Unfortunately many employees have access to the SQL DB that do not understand the power of SQL or are not that familiar with SQL statements. I have already developed the Query to pull all the necessary information should someone accidently make a mistake (EX: writing an UPDATE statement without specifying a WHERE clause --which has happened many times before), but now I am trying to write a script that will use the information I pulled from above and RE-INSERT the old value back into the appropriate table. The problem is that I am going to need to update multiple rows with DIFFERENT values. I know that I can do it like this:

/*
UPDATE mv
SET price = CASE WHEN mvid = 10000002 THEN 9.37
WHEN mvid = 10000015 THEN 5.99
WHEN mvid = 10000001 THEN 10.98
WHEN mvid = 10000016 THEN 1.00
ELSE 0
END
WHERE mvid IN (10000008,10000002,10000015,10000001,10000016)
*/

The problem with this is each value must be individually written in the statement, which is fine if someone only updates 5 or 10 rows with the wrong value. However, if someone should update 20,000 rows I need a better way to do this.

Does anyone have any ideas on how to pull from a select and update multiple rows? I know that updates do not work across multiple tables, but is there a way to do multiple rows/different values? Any suggestions are greatly appreciated.

 

[micha123]

Is there a logic there whithin the command:

CASE WHEN mvid = 10000002 THEN 9.37
WHEN mvid = 10000015 THEN 5.99
WHEN mvid = 10000001 THEN 10.98
WHEN mvid = 10000016 THEN 1.00

(How do you know all these values and their replacements?)

Maybe the solution to your problem is joining two tables, or a table to itself using the fields in the case statement, such as:

CASE WHEN mvid = FLD1 THEN FLD2
END
FROM TB1 LEFT OUTER (?) JOIN TB2

Please be more specific on defining the problem.

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.