Search code examples
sqlsql-servert-sqlsql-updatesql-insert

Insert value refer to other column

Here is original table A

Currency DM_LS ProductID TimeID
TWD 1 26 559
TWD 1 26 560
TWD 1 27 561
TWD 2 27 562
TWD 2 28 563
TWD 2 28 564

I would like to generate serial number from table A above. So I add new column named SerialNum

Now I have no idea how to generate and insert the value. Value is Currency + DM_LS + ProductID + TimeID

Does it work through SQL?

Desired Result

Currency DM_LS ProductID TimeID SerialNum
TWD 1 26 559 TWD126559
TWD 1 26 560 TWD126560
TWD 1 27 561 TWD127561
TWD 2 27 562 TWD227562
TWD 2 28 563 TWD228563
TWD 2 28 564 TWD228564

Thanks so much.

Solution

  • You can do it two ways:

    1. Add computed column
    ALTER TABLE TABLEA ADD SerialNum AS CONCAT(Currency, DM_LS, Productid, TimeID)
    1. Add separate column
    ALTER TABLE TABLEA ADD SerialNum VARCHAR(200);

UPDATE TABLEA
SET SerialNum = CONCAT(Currency, DM_LS, Productid, TimeID)