Updating multiple rows with different values
-
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.
-
So if I understand this utterly nightmarish (employees have direct access ?!?! they update without specifying a "where" clause ?!?!) situation correctly:
1) Your change log table captures the old value and the changed value ?! What is the ddl of this table - is it linked to your mv table by mvid ?!
2) Are there any other tables involved ?!
3) Is there no way you can get the users to change ONLY THROUGH THE UI ?!?! I seriously think I would quit my job if I had to deal with something like this! You are a brave soul!
4) Could you please post the ddls and sample data from all tables involved !
**ASCII stupid question, get a stupid ANSI !!!** -
You also don't mention if this is the only table that the employees "mess around with"!!
What kind of db backup plan do you have ?! You could try using differentials or transaction logs if you know that they usually attempt these changes (eg
from 9 to 5, Mon - Fri or some such predictable usage pattern!
**ASCII stupid question, get a stupid ANSI !!!** -
3) Is there no way you can get the users to change ONLY THROUGH THE UI ?!?! I seriously think I would quit my job if I had to deal with something like this! You are a brave soul!
This is how it is around here and I'm not quiting. However I must admit that I locked everyone out that could not be trusted. I have only 2 other persons that have direct table access but they also have a programming course (basic). The big thing is that they understand that things can go wrong if they are not carefull. They require to have direct access because the program just doesn't deliver what they need and we decided to recode instead of continuously repairing the old one. So it's gona stay like that for a while.
-
Okay Remi - you're a better man than I
(and NO - je ne suis pas un ts...remember we've been through this before!)Hopefully your recoding speed is as fast as your "providing solutions to ssc" speed...and then all will be fine....
**ASCII stupid question, get a stupid ANSI !!!** -
I'm fast but the analysis is a monster project. The current project has 300+ forms and reports. And it corresponds to only a third of the needs of the users...
What a ts??
-
How quickly you forget...you were the one who coined the term...don't want to spell it out...but remember when you asked me if I was not a ts & I got (kinda) mad....
**ASCII stupid question, get a stupid ANSI !!!** -
I have one guy who can't remember that when you cut-and-paste from Excel into an EM grid you get a trailing carriage return. So I had to add this check constraint on a table he maintains:
(not([field1] like ('%' + char(13) + '%') or [field2] like ('%' + char(13) + '%') or [field3] like ('%' + char(13) + '%') or [field4] like ('%' + char(13) + '%') or [field5] like ('%' + char(13) + '%') or [field6] like ('%' + char(13) + '%') or [field7] like ('%' + char(13) + '%') or [field8] like ('%' + char(13) + '%') or [field9] like ('%' + char(13) + '%') or [field10] like ('%' + char(13) + '%') or [field11] like ('%' + char(13) + '%') or [field12] like ('%' + char(13) + '%') or [field13] like ('%' + char(13) + '%')))
INSTEAD OF triggers are also good for catching some common errors.
Of course it would be better to have everything locked down in SPs and take EM away from the lusers, but I don't have time to write maintenance code for everything they do. And I certainly wouldn't trust them to write it.
-
You're gonna to refresh my memory because I don't remember that. you can PM me id you don't want this to be redisplayed.
-
Well, I have to say that I am 'glad' (or maybe 'sad') that other companies can have this problem too. Unfortunately most employess have direct access through QA and EM to the ENTIRE database.
I currently work on the application support staff, but I am slowly transitioning to the DBA group. One of my developers has a Query that almost does what I need it to do so I asked him to add a few things to almost fully automated the process. I will let you guys know when it is done if you are interested to see it. Also, the changed log table does not link to MV with MVID and there are approximately 90 other tables that write to changelog. In addition, ChangleLog is actually made up of 3 tables: ChangeLogAction, ChangeLogRow, ChangeLogColumn. It is a really powerful tool, which my company has entertained the idea of marketing as a seperate product.
I am not a SQL-pro by any means, so I really appreciated all the help you guys have given.
-ADAM
-
Well, I have to say that I am 'glad' (or maybe 'sad') that other companies can have this problem too. Unfortunately most employess have direct access through QA and EM to the ENTIRE database.
I currently work on the application support staff, but I am slowly transitioning to the DBA group. One of my developers has a Query that almost does what I need it to do so I asked him to add a few things to almost fully automated the process. I will let you guys know when it is done if you are interested to see it. Also, the changed log table does not link to MV with MVID and there are approximately 90 other tables that write to changelog. In addition, ChangleLog is actually made up of 3 tables: ChangeLogAction, ChangeLogRow, ChangeLogColumn. It is a really powerful tool, which my company has entertained the idea of marketing as a seperate product.
I am not a SQL-pro by any means, so I really appreciate all the help you guys have given.
-ADAM
-
Hmm. Are your jobs so great otherwise that you havn't thought of changing them?
There are lots of SQL DBA positions to be filled where you do not have to deal with these issues. I guess you can always try to change how things are done at your work place. But after a while it gets tiresome talking to a wall. Which often is the case where these problems can be found. In my book, after you have tried to make them see the light ( that they are throwing away money on stupidity, let's face it, how much useful things can be done instead of trying to invent a fool-proof, automatic way of rolling back changes that were made by mistake? ) for a while, it could really be time to search for a new job. Why not start sending CVs around and see what kind of response your get?
//Hans
-
We use replication to copy data to another database, and then users can query that other database instead of production. They only have read access, so they can select whatever they want for reporting without affecting the live data.
-
I like the replicated DB idea, and most clients either run replication or restore a backup of the live database to the test server for Q & A. The problem is most of the time, my fellow employees are updating information for clients in the in the live DB.
ADAM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply