| Author |
 Topic  |
|
|
GJT
Starting Member
3 Posts |
 Posted - 03/07/2013 : 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 4
10000 20.00 1 0
10001 0 0 1
10002 15.00 1 0
10003 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 = 10000
Is it possible to do this in a single statement?
Thanks,
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1418 Posts |
Posted - 03/07/2013 : 07:31:08
|
Can you post the expected result after UPDATE?
--
Chandu |
 |
|
|
GJT
Starting Member
3 Posts |
Posted - 03/07/2013 : 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 4
10000 20.00 1 0
10001 20.00 1 0
10002 20.00 1 0
10003 20.00 1 0
|
|
|
|
GJT
Starting Member
3 Posts |
Posted - 03/08/2013 : 09:56:17
|
DECLARE @Col2 nchar(10),@Col3 nchar(10),@Col4 nchar(10)
SELECT @Col2 = [Col 2],@Col3 = [Col 3],@Col4 = [Col 4]
FROM dbo.Table_1
WHERE [Col 1] = 10000
UPDATE dbo.Table_1
SET [Col 2] = @Col2,
[Col 3] = @Col3,
[Col 4] = @Col4
WHERE [Col 1] in (10001,10002,10003)
The above did exactly what was required. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1418 Posts |
Posted - 03/11/2013 : 07:20:36
|
--Your Sample Data
DECLARE @tab TABLE(Col1 INT, Col2 DEC(5,2), Col3 INT, Col4 INT)
INSERT INTO @tab
SELECT 10000, 20.00, 1, 0 UNION ALL
SELECT 10001, 0, 1, 0 UNION ALL
SELECT 10002, 15.00, 1, 0 UNION ALL
SELECT 10003, 0, 0, 0
-- You can do above (GJT Posted - 03/08/2013 : 09:56:17 ) by using UPDATE statement without additonal Variables
UPDATE @tab
SET [Col2] = t.Col2,
[Col3] = t.Col3,
[Col4] = t.Col4
FROM (SELECT * FROM @tab WHERE Col1 =10000) t
SELECT * FROM @tab
--
Chandu |
|
|
| |
Topic |
|
|
|
Update multiple columns in multiple rows
