Search code examples
sqlsql-serversql-server-2012qsqlquery

How to update a column from another column in the same table in a list of rows?

I have the following data structure in Table A:

RequestId  |  Serial  |  RowSerial
-----------+----------+----------
   1       |    1     |    NULL
   1       |    2     |    NULL
   1       |    3     |    NULL

I need a query to have the following update:

RequestId  |  Serial  |  RowSerial
-----------+----------+----------
   1       |    1     |    501
   1       |    2     |    502
   1       |    3     |    503

500 is a static number that is added to the Serial column and is set to RowSerial column.

I have tried this:

UPDATE Table A
SET RowSerial=(SELECT top 1 500+(Serial) FROM Table A where requestid=1 and RowSerial is Null) where requestid=1

But it didnt work. How to do this?

Solution

  • A simple addition:

    UPDATE TableA
SET RowSerial = 500 + Serial
WHERE RequestId = 1 AND RowSerial IS NULL

    use the WHERE part only if you want to restrict the update to specific rows.