February 6, 2013 at 10:38 pm
I want to update data on condition based, if condition met then only update else left it.
I was using CASE statement but i realized in CASE there is no way to skip it if condition dont meet.
For example:-
UPDATE tbl1
SET column1 = CASE WHEN @Values = 1 THEN @Values ELSE column1 END
On the above script it will always update, difference is that if condition will meet in that case it will update to @values else the same column value, but i want to skip in case it does not meet condition.
Because column1 have the indexes and it is costing to update this column.
February 6, 2013 at 11:43 pm
You can't do that using the case.....The case would perform the Update operation. To avoid the Update as a whole you need to wrap the Update statement in an If...Then.
The following link might help you:
February 6, 2013 at 11:54 pm
I need to update multiple column, so i dont think IF ELSE is good thing to implement, have to check for each column in separate statement.
February 7, 2013 at 12:06 am
In your example if @Values=1 then every row in the table will be updated to @Value correct?
Update Table
Set Column1=@values
where @values=1
will do exactly the same as your example, but something in the back of my head says this isnt exactly what you are after.
Do you have the real life case that you are working on as that will help people give you better advice, as it might be that a Merge statement is a better option.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 7, 2013 at 12:09 am
UPDATE tbl1
SET
column1 = CASE WHEN @Values1 = 1 THEN @Values1 ELSE column1 END,
column2 = CASE WHEN @Values2 = 1 THEN @Values2 ELSE column2 END,
column3 = CASE WHEN @Values3 = 1 THEN @Values3 ELSE column3 END
February 7, 2013 at 12:41 am
This looks like you want to the column in all rows on a table to 1 dependant on the value of a Paramater. correct?
I would suggest using Dynamic SQL to build the update statement
Declare @SQL nvarchar(4000),@comma nchar(1)=''
Declare @Value1 int,@Value2 int,@Value3 int
Set @Value1=0
Set @Value2=1
Set @Value3=1
Set @SQL = 'UPDATE tbl1 SET '
IF @Value1=1
Begin
SET @SQL = @SQL+' Column1='+convert(nvarchar,@Value1)
set @comma=','
End
IF @Value2=1
Begin
SET @SQL = @SQL+ @comma + ' Column2='+convert(nvarchar,@Value2)
set @comma=1
set @comma=','
End
IF @Value3=1
Begin
SET @SQL = @SQL+ @comma + ' Column3='+convert(nvarchar,@Value3)
End
Print @SQL
This will build the the update based on the state of the variable, so that you will only update the columns that you need to.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 7, 2013 at 12:51 am
purushottam2 (2/6/2013)
I want to update data on condition based, if condition met then only update else left it.I was using CASE statement but i realized in CASE there is no way to skip it if condition dont meet.
For example:-
UPDATE tbl1
SET column1 = CASE WHEN @Values = 1 THEN @Values ELSE column1 END
On the above script it will always update, difference is that if condition will meet in that case it will update to @values else the same column value, but i want to skip in case it does not meet condition.
Because column1 have the indexes and it is costing to update this column.
How many columns and variables are there to compare ;
are you not able to filter out records while updating it ?
column1 = CASE WHEN @Values1 = 1 THEN @Values1 ELSE column1 END,
column2 = CASE WHEN @Values2 = 1 THEN @Values2 ELSE column2 END,
column3 = CASE WHEN @Values3 = 1 THEN @Values3 ELSE column3 END
updating column1 to column1; definitely not a good idea .
can you explain it a little bit more ; why do you want to acheive this update ?
can yo post the code where in @value gets generated ???
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
February 7, 2013 at 2:02 am
-- @NotificationsStatus is table type variable
UPDATE n
SET
n.Name = ns.Name,
n.EmpCode = ns.EmpCode,
n.EmailProcessingStatus =
CASE WHEN ns.EmailProcessingStatus IS NOT NULL THEN ns.EmailProcessingStatus ELSE n.EmailProcessingStatus END,
n.MobileProcessingStatus =
CASE WHEN ns.MobileProcessingStatus IS NOT NULL THEN ns.MobileProcessingStatus ELSE n.MobileProcessingStatus END,
n.UpdatedDate = GETUTCDATE()
FROM [notification] n INNER JOIN @NotificationsStatus ns ON n.Id = ns.NotificationId
February 7, 2013 at 2:46 am
demonfox (2/7/2013)
...updating column1 to column1; definitely not a good idea...
There was a thread covering exactly this, fairly recently. I'll try to track it down. IIRC the conclusion (for most folks) was this; unless column1 is part of a clustered index key, then nothing happens i.e. the page isn't dirtied and the change (or not-change, whichever you prefer) isn't written to disk.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 7, 2013 at 2:57 am
ChrisM@Work (2/7/2013)
demonfox (2/7/2013)
...updating column1 to column1; definitely not a good idea...There was a thread covering exactly this, fairly recently. I'll try to track it down. IIRC the conclusion (for most folks) was this; unless column1 is part of a clustered index key, then nothing happens i.e. the page isn't dirtied and the change (or not-change, whichever you prefer) isn't written to disk.
is that so ? that's good thing to know ; I will also try to search on that . I guess, discussion would have followed up my next question in support of not doing column1=column1....
thanks for heads up chris..
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
February 7, 2013 at 2:59 am
demonfox (2/7/2013)
ChrisM@Work (2/7/2013)
demonfox (2/7/2013)
...updating column1 to column1; definitely not a good idea...There was a thread covering exactly this, fairly recently. I'll try to track it down. IIRC the conclusion (for most folks) was this; unless column1 is part of a clustered index key, then nothing happens i.e. the page isn't dirtied and the change (or not-change, whichever you prefer) isn't written to disk.
is that so ? that's good thing to know ; I will also try to search on that . I guess, discussion would have followed up my next question in support of not doing column1=column1....
thanks for heads up chris..
No worries. I can't find the thread, but Paul White did an excellent job of writing it up on his blog.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 7, 2013 at 3:40 am
No worries. I can't find the thread, but Paul White did an excellent job of writing it up on his blog.
thanks again ; will read that out ..
And coming back to the post
UPDATE tbl1
SET column1 = CASE WHEN @Values = 1 THEN @Values ELSE column1 END
On the above script it will always update, difference is that if condition will meet in that case it will update to @values else the same column value, but i want to skip in case it does not meet condition.
I suppose the link mentioned by chris could answer the question ..
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
February 7, 2013 at 3:56 am
I know the one you're talking about GSquared, Gail made a lot of very interesting points and I think Paul also added a few comments about inplace updates.
The only time they have an effect is if they Alter an index if I remember the thread correctly.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 7, 2013 at 4:10 am
Jason-299789 (2/7/2013)
I know the one you're talking about GSquared, Gail made a lot of very interesting points and I think Paul also added a few comments about inplace updates.The only time they have an effect is if they Alter an index if I remember the thread correctly.
Thanks jason for the search string ..
I found the link ; special thanks to google for that 😀
http://www.sqlservercentral.com/Forums/Topic1343937-392-1.aspx
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
February 8, 2013 at 9:57 am
-- @NotificationsStatus is table type variable
UPDATE n
SET
n.Name = ns.Name,
n.EmpCode = ns.EmpCode,
n.EmailProcessingStatus =
CASE WHEN ns.EmailProcessingStatus IS NOT NULL THEN ns.EmailProcessingStatus ELSE n.EmailProcessingStatus END,
n.MobileProcessingStatus =
CASE WHEN ns.MobileProcessingStatus IS NOT NULL THEN ns.MobileProcessingStatus ELSE n.MobileProcessingStatus END,
n.UpdatedDate = GETUTCDATE()
FROM [notification] n INNER JOIN @NotificationsStatus ns ON n.Id = ns.NotificationId
This is admittedly no longer a single statement, but it should only update the records in which you're interested. The WHERE clauses will limit the joins and only run the updates for records in need, and never a need to set a column to its current value. If you wouldn't ever have a situation where you'd update just EmailProcessingStatus or MobileProcessingStatus, you could skip the second and third statements.
UPDATE n
SET n.Name = ns.Name,
n.EmpCode = ns.EmpCode,
n.EmailProcessingStatus = ns.EmailProcessingStatus,
n.MobileProcessingStatus = ns.MobileProcessingStatus,
n.UpdatedDate = GETUTCDATE()
FROM [notification] n
INNER JOIN @NotificationsStatus ns
ON n.Id = ns.NotificationId
where ns.EmailProcessingStatus is not null
and ns.MobileProcessingStatus is not null;
UPDATE n
SET n.Name = ns.Name,
n.EmpCode = ns.EmpCode,
n.EmailProcessingStatus = ns.EmailProcessingStatus,
n.UpdatedDate = GETUTCDATE()
FROM [notification] n
INNER JOIN @NotificationsStatus ns ON n.Id = ns.NotificationId
where ns.EmailProcessingStatus is not null
and ns.MobileProcessingStatus is null;
UPDATE n
SET n.Name = ns.Name,
n.EmpCode = ns.EmpCode,
n.MobileProcessingStatus = ns.MobileProcessingStatus,
n.UpdatedDate = GETUTCDATE()
FROM [notification] n
INNER JOIN @NotificationsStatus ns
ON n.Id = ns.NotificationId
where ns.EmailProcessingStatus is null
and ns.MobileProcessingStatus is not null;
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply