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 |
GJT
Starting Member
3 Posts |
Posted - 2013-03-07 : 07:28:00
|
I have a table where I need to update multiple columns in each record (multiple rows) to match a single record already in the table.For example:Col 1 Col 2 Col 3 Col 410000 20.00 1 010001 0 0 110002 15.00 1 010003 0 0 0 So, for the above I would like to update values in Col 2, Col 3, Col 4 for records in (10001, 10002, 10003) to match values in Col 2, Col 3, Col 4 where Col 1 = 10000Is it possible to do this in a single statement?Thanks, |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-07 : 07:31:08
|
Can you post the expected result after UPDATE?--Chandu |
|
|
GJT
Starting Member
3 Posts |
Posted - 2013-03-07 : 08:04:39
|
Thanks for the reply.I would like the values in Col 2, Col 3 and Col 4 for rows relating to Col 1 IN (10001,10002,10003) to be the same as values in Col 1 = 10000 if that makes sense...Col 1 Col 2 Col 3 Col 410000 20.00 1 010001 20.00 1 010002 20.00 1 010003 20.00 1 0 |
|
|
GJT
Starting Member
3 Posts |
Posted - 2013-03-08 : 09:56:17
|
[code]DECLARE @Col2 nchar(10),@Col3 nchar(10),@Col4 nchar(10)SELECT @Col2 = [Col 2],@Col3 = [Col 3],@Col4 = [Col 4]FROM dbo.Table_1WHERE [Col 1] = 10000 UPDATE dbo.Table_1 SET [Col 2] = @Col2, [Col 3] = @Col3, [Col 4] = @Col4 WHERE [Col 1] in (10001,10002,10003) [/code]The above did exactly what was required. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-11 : 07:20:36
|
--Your Sample DataDECLARE @tab TABLE(Col1 INT, Col2 DEC(5,2), Col3 INT, Col4 INT)INSERT INTO @tabSELECT 10000, 20.00, 1, 0 UNION ALLSELECT 10001, 0, 1, 0 UNION ALLSELECT 10002, 15.00, 1, 0 UNION ALLSELECT 10003, 0, 0, 0-- You can do above (GJT Posted - 03/08/2013 : 09:56:17 ) by using UPDATE statement without additonal VariablesUPDATE @tabSET [Col2] = t.Col2, [Col3] = t.Col3, [Col4] = t.Col4 FROM (SELECT * FROM @tab WHERE Col1 =10000) tSELECT * FROM @tab --Chandu |
|
|
|
|
|
|
|