Update multiple columns in multiple rows

• gj.thorpe

  Grasshopper

  Points: 22

  More actions

  March 8, 2013 at 3:54 am

  #290601

  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?

• ryan.mcatee

  SSC Eights!

  Points: 860

  More actions

  March 8, 2013 at 6:03 am

  #1594934

  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.

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  March 8, 2013 at 6:51 am

  #1594948

  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.

• gj.thorpe

  Grasshopper

  Points: 22

  More actions

  March 8, 2013 at 7:53 am

  #1594969

  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.