Multiple Set & Where Clause - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Set & Where Clause

Author

Topic

paullyie
Starting Member

2 Posts

Posted - 2010-04-28 : 16:02:16

Hey Everyone
New to the forums and trying to get my head around MSSQL
Hope I have this in the right forum

Just a quick question
I'm trying to run an update statement

basically I want to update a column using multiple set & multiple where clause;

Example like below:

UPDATE TABLE SET COLUMN 1 = VALUE1 WHERE COLUMN2 = ID1
UPDATE TABLE SET COLUMN 1 = VALUE2 WHERE COLUMN2 = ID2
UPDATE TABLE SET COLUMN 1 = VALUE3 WHERE COLUMN2 = ID3
UPDATE TABLE SET COLUMN 1 = VALUE4 WHERE COLUMN2 = ID4
UPDATE TABLE SET COLUMN 1 = VALUE5 WHERE COLUMN2 = ID5
UPDATE TABLE SET COLUMN 1 = VALUE6 WHERE COLUMN2 = ID6

Is it possible to run this type of statement?
The syntax verifies fine against the data but then it throws an error
when executed

Any help apprecitated,
Cheers
Paul

Sachin.Nand

2937 Posts

Posted - 2010-04-28 : 16:05:37

What is the error?

PBUH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 16:06:31

Yes that is fine, assuming "COLUMN 1" becomes "COLUMN1" (probably just a typo). What error is thrown?

You could also write one update statement with CASE included.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-04-28 : 16:11:53

If column 1 is truly "spelled column 1", enclose it in brackets, ie. UPDATE TABLE SET [COLUMN 1] = VALUE1 WHERE COLUMN2 = ID1. Also, "table" is a reserved word so be sure it's correct.

You can also do this in one statement as follows:

UPDATE [table] SET [Column 1] =
CASE WHEN Column2 = ID1 then Value1
WHEN Column2 = ID2 then Value2
WHEN Column2 = ID3 then Value3
WHEN Column2 = ID4 then Value4
WHEN Column2 = ID5 then Value5
WHEN Column2 = ID6 then Value6
END

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

paullyie
Starting Member

2 Posts

Posted - 2010-04-28 : 16:22:28

Thanks for the replies guys,

I'll test out the suggestions

I looked into the error (which I should have done before)
I think its not accepting becuase the field is unique ???

Error Source .Net SqlClient Data provider
Error Message: Cannot insert duplicate key row in object dbo.table with unique index
'IX_Table_Value'
The statement has been terminated

anyone know what this means

Thanks again for the help

Sachin.Nand

2937 Posts

Posted - 2010-04-28 : 16:27:02

It means you are trying to insert duplicate values in the column having unique index.

PBUH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 16:28:25

The suggested code changes won't help with that error. You'll need to make the updated data unique in order to avoid the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources