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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update multiple columns in multiple rows

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 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-07 : 07:31:08
Can you post the expected result after UPDATE?

--
Chandu
Go to Top of Page

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 4
10000 20.00 1 0
10001 20.00 1 0
10002 20.00 1 0
10003 20.00 1 0


Go to Top of Page

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_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) [/code]

The above did exactly what was required.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-11 : 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
Go to Top of Page
   

- Advertisement -