March 8, 2013 at 3:54 am
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 1Col 2Col 3Col 4
1000020.0010
10001001
1000215.0010
10003000
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 1Col 2Col 3Col 4
1000020.0010
1000120.0010
1000220.0010
1000320.0010
Is it possible to do this in a single statement?
March 8, 2013 at 6:03 am
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.
March 8, 2013 at 6:51 am
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
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
WHERE [Col 1] = 10000
UPDATE
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.
March 8, 2013 at 7:53 am
David,
Thanks very much.
DECLARE @Col2 decimal,@Col3 int,@Col4 int
SELECT @Col2 = [Col 2],@Col3 = [Col 3],@Col4 = [Col 4]
FROM
WHERE [Col 1] = 10000
UPDATE
SET [Col 2] = @Col2,
[Col 3] = @Col3,
[Col 4] = @Col4
WHERE [Col 1] > 10000
Did exactly what was required.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply