Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update multiple columns in multiple rows Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 3:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 12:18 AM
Points: 2, Visits: 15
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

I would like the data to end up like:

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

Is it possible to do this in a single statement?
Post #1428500
Posted Friday, March 8, 2013 6:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:39 AM
Points: 47, Visits: 133
I think you want something like this?

UPDATE {table}
SET [Col 2]=(SELECT [Col 2] FROM {table} WHERE [Col 1]=10000)
, [Col 3]=(SELECT [Col 3] FROM {table} WHERE [Col 1]=10000)
, [Col 4]=(SELECT [Col 4] FROM {table} WHERE [Col 1]=10000)


Edit: Misread this. And I am assuming [Col 1] is unique.
Post #1428543
Posted Friday, March 8, 2013 6:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 6,954, Visits: 7,074
ryan.mcatee (3/8/2013)
I think you want something like this?


or

UPDATE a 
SET a.[Col 2] = b.[Col 2],
a.[Col 3] = b.[Col 3],
a.[Col 4] = b.[Col 4]
JOIN [table] b ON b.[Col 1] = 10000
WHERE a.[Col 1] > 10000

or

DECLARE @Col2 decimal,@Col3 int,@Col4 int
SELECT @Col2 = [Col 2],@Col3 = [Col 3],@Col4 = [Col 4]
FROM [table]
WHERE [Col 1] = 10000
UPDATE [table]
SET [Col 2] = @Col2,
[Col 3] = @Col3,
[Col 4] = @Col4
WHERE [Col 1] > 10000




Far away is close at hand in the images of elsewhere.

Anon.

Post #1428560
Posted Friday, March 8, 2013 7:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 12:18 AM
Points: 2, Visits: 15
David,

Thanks very much.

DECLARE @Col2 decimal,@Col3 int,@Col4 int
SELECT @Col2 = [Col 2],@Col3 = [Col 3],@Col4 = [Col 4]
FROM [table]
WHERE [Col 1] = 10000
UPDATE [table]
SET [Col 2] = @Col2,
[Col 3] = @Col3,
[Col 4] = @Col4
WHERE [Col 1] > 10000

Did exactly what was required.
Post #1428585
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse