Conditional Update Quandary
-
I have a table (any table will do) that has an ID column (the primary key) and a bunch of other columns (doesn’t matter what they are). Let’s say, for purposes of discussion, that the table has 80 or so columns (not my idea folks…). Like this…
ID Col001 Col002 …etc… Col079 Col080
I needed to come up with a conditional update that would, because of other 3rd party referential integrity triggers (again, not my idea), determine the 1 to 80 columns that would be updated and do the update using a single update instead of one update per column. For example, if columns 1 through 20, 28, 38, and 78 needed to be updated (according to a bitmask passed to the proc, still not my idea :sick
, the proc should update all those columns using a single update. If a different bitmask were passed in, I’d need to update all those columns identified by the different bitmask. Again, the trick is that I have to do it with a single update each time the proc is passed a bitmask.I came up with a really cool Dynamic SQL solution that works great at generating the appropriate UPDATE statement and then executing it. My problem is that I can’t use it because the client absolutely forbids the use of Dynamic SQL.
Does anyone have any idea how to do such a thing (programmable multicolumn single update) with tables that have a large number of columns without using Dynamic SQL? I’m out of ideas and could sure use some help.
Thanks…
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff, I do not envy you that problem!
Does your solution have to remain entirely within SQL Server? Might the client accept a DLL which you can call as an extended stored proc?
Otherwise, without dynamic SQL, I've no idea ...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
If I get a vague definition of what an update on a table is supposed to do I carry out something similar to the SQL below:
CREATE PROC dbo.setTable @id Int, @param1 = NULL ......@param99 = NULL
--Where a param is null accept the current record value.
SELECT @param1 = ISNULL(@param1,Col01) ,
...
...
@param99 = ISNULL(@param99,Col99) ,
FROM dbo.MyTable
WHERE id = @id
UPDATE dbo.MyTable
SET Col01 = @param1 ,
...
..
Col99 = @param99
WHERE id = @id
Of course if you want to set a column to NULL then this won't work, in which case you need to be able to pass an arbitary value that will never crop up in the real world and use additional value checking.
-
Phil,
I sure wouldn't mind seeing that DLL if I can call it as an extended stored procedure. I hadn't thought about that. From a Data Troll aspect, I wouldn't mind seeing the source code either. Either way, that would be outstanding because I could remain in the immediate realm of SQL without making a trip to the command shell and it wouldn't be perceived as Dynamic SQL.
David,
On a bit of a different slant, I might be able to make something similar to your idea work... but I have to be real careful not to update columns that aren't included in the update because the 3rd party triggers will still fire on those columns even if the value of the column hasn't changed (like any trigger will).
I'm toying with the idea of an Instead-of-update trigger but I keep slamming into the same wall of being able to make a programmable update that will only update the columns required without using Dynamic SQL so the DLL Phil mentioned might just be the ticket.
Thank you both for you ideas and your help.
"We're all in this together and I'm pullin' for ya" -- Red Green
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Uhhh... that's really something. Any other restrictions, like for example that you may not use the word "UPDATE" in your SQL?
I can only agree that the only way seems to be to do it outside SQL Server like Phil suggested.
-
Not my restrictions but the I agree with your smart-assed comment and useless comment... the client is insane especially in light of the fact that I have a solution that does actually work.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I'm sorry if my comment was offending or uncalled for, and I apologize for it. It was meant more like "I feel with you and I wouldn't want to be in your position"... I tried to think of some other solution, but the requirements seemed so ridiculous and blocking any sensible approach, that I expressed my thoughts this way. I never mentioned that the requirements would be yours, I understand very well that these were imposed on you. Also I'd like to explain, that i'm not native English speaker and sometimes I may choose incorrect formulations, especially when the things get a bit emotional. I promise I'll try to be more helpful and less emotional next time.
Good luck,
Vladan
-
Hi Vladan - it's OK, I think Jeff's having a bad day. I am native English and his comments were a little bit harsh - but I think we understand why, having seen the conditions he's working with
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Below is a really stupid solution but it does work under your restrictions.
Use your existing Dynamic SQL solution to generate a stored procedure for every possible combination of the bitmask. Then a driving SP would call the appropriate SP based on the passed bitmask value.
You would then have 80 sps that update only one column, and 3081
sps to update 2 columns, etc
I think this works out to 164,322 stored procedures.
As a quick guess, the average size of the source would be about 1K, so you will need only 164 Mb of additional disk space.
declare @i bigint, @f bigint
set @i = 79
set @f = 0
while @i > 0
begin
set @f = @f + (@i * ( @i - 1 ))
set @i = @i - 1
end
select @f + 2 -- One for the driver and one to update all columns
SQL = Scarcely Qualifies as a Language
-
Vladan,
My most sincere appologies as well... I thought you were directing it at me... Phil's correct... it's been a terrible week never mind day alone. But that's no excuse and I'm sorry about that. Thank you for taking the time to explain what you meant... I feel a bit of a heel
for taking it the wrong way and should have known better especially from our mutual postings in the past.Phil,
What can I say... always the gentleman. Thanks for the feedback.
Carl,
Man, thanks... considering the restrictions they put on me, I'll try anything... I might even try packing a little gun-cotton in the client's ears and see how long he can hold a lit cherry-bomb in his mouth
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hey Jeff:
I think we have all had to deal with Clients who have decided to impose an architecture where not all ramifications have been considered.
When you present a solution that meets their architectural specifications but is obviously ridiculous, such as having 100,000+ different stored procedures to update a single table, they may reconsider the architecture.
Good Luck
SQL = Scarcely Qualifies as a Language
-
What datatype they want to use for bitmask?
power(2, 79) exceeds bigint max value.
declare @two bigint
set @Two = 2
UPDATE Table
SET col01 = case when @bitmask&1 = 0 then col01 else @Newcol01Value,
col02 = case when @bitmask&2 = 0 then col01 else @Newcol02Value,
....
col63 = case when @bitmask&power(@Two, 62) = 0 then col63 else @NewCol63Value
WHERE ID = ...
OR for multirow update
UPDATE T
SET col01 = case when @bitmask&1 = 0 then T.col01 else N.col01,
col02 = case when @bitmask&2 = 0 then T.col01 else N.col02,
....
col63 = case when @bitmask&power(@Two, 62) = 0 then T.col63 else N.Col63
FROM Table T
INNER JOIN NewValuesTable N on T.ID = N.Id
_____________
Code for TallyGenerator -
They want to use a varbinary similar to what is presented by COLUMNS_UPDATED() in triggers. I've got all that worked out so no problem there. Like I said, I have a working solution using Dynamic SQL but they have an insane fear of the security risks involved. Everyone one has come up with awesome ideas to try to work around that restriction but I think the best bet is for me to simply educate the client as to the obvious and overwhelming advantages that Dynamic SQL has in this situation, as well as how to minimize the risks of using it.
The problem with the alternate solutions (except the 100,000 procs
) is that they will cause the downstream triggers to fire even if the value stays the same as in Serqiy's great example. I even told them I can keep that from happening (the triggers will still fire but NOT take an action unless there's an actual change) if I could add a little code to the front of each trigger... no such luck... more restrictions.Although the particular client is important to me, I guess I don't need one that going to bust my chops so hard when they're the ones that haven't done the adequate homework. I'm going to make the attempt to educate them and if they persist with their insanes fears and restrictions, I'm just gonna say goodbye. They'll call back for the working solution after they go through a few more folks with the same restrictions (or not).
Anyway's, thanks for trying guys... your the best.
And Vladan, sorry again about the huge snap I popped on you...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
That's OK, Jeff, don't mention it :-). No need to talk about it any more, no hard feelings on either side and that's all that's important. Hope you will be able to educate the client, I think your decision is the best you could make in this situation.
bye, Vladan
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply