SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update multiple columns in multiple rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GJT
Starting Member

3 Posts

Posted - 03/07/2013 :  07:28:00  Show Profile  Reply with Quote
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
2202 Posts

Posted - 03/07/2013 :  07:31:08  Show Profile  Reply with Quote
Can you post the expected result after UPDATE?

--
Chandu
Go to Top of Page

GJT
Starting Member

3 Posts

Posted - 03/07/2013 :  08:04:39  Show Profile  Reply with Quote
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 - 03/08/2013 :  09:56:17  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/11/2013 :  07:20:36  Show Profile  Reply with Quote
--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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000