Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
vinylpimp
Starting Member
16 Posts |
Posted - 2013-04-30 : 12:53:45
|
Currently I update one entry at a time ListingId Price 12345678 087654321 1Update [XXX].[dbo].[listing]set Price = '0'where ListingId in ('12345678')thenUpdate [XXX].[dbo].[listing]set Price = '1'where ListingId in ('87654321')Is there a way to do both executions with one command?Many thanks! |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-30 : 13:56:47
|
You may want to try something like this:[CODE]UPDATE [XXX].[dbo].[listing]SET Price = CASE WHEN ListingId in ('12345678')THEN '0' WHEN ListingId in ('87654321')THEN '1' ELSE PriceENDWHERE ListingId in ('12345678', '87654321');[/CODE] |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-04-30 : 13:59:30
|
update listingset Price = case when listingid = 12345678 then 0 else 1 endwhere listingid in(12345678,87654321) Too old to Rock'n'Roll too young to die. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-30 : 14:15:13
|
To add to what MuMu suggested, the thought is that, if you had a criterion that tells you which ListingID's should get zero's and which should get ones, you can use those in the WHEN expressions. As an example (and I am making up this out of thin air), let us say you had a column called expiry_date. Also, let us assume that if expiry_date is earlier than now, you want to mark listing id as 0, otherwise set it to 1. Then, you would modify MuMu's WHEN expressions like this:.... WHEN expiry_date < getdate() THEN '0' WHEN expiry_date >= getdate() THEN '1'.... |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 2013-04-30 : 17:53:20
|
Thank you so much guys, all scenarios are pretty much covered! |
|
|
|
|
|