0

I would like to update a table but the problem I have is the following: I have a table with more than 60K primary keys in a column called TerminalID. This table has like 10 more columns, but I would like to update a column called SerialNumber, but the problem is that the serial number is not the same for every primary key.

The problem is that the update SQL code below will apply the same serial number to all the terminalID's I have and not a different to each PK.

Any idea?

Use [testserver]

update [testtable]
set SerialNumber =''
from [testtable]
where [TerminalID] in ( ... )


terminalID    Serial Number
463328        215728108
168734        213808561
160879        214621937
451991       215523741

This is the only way I can think now , do it one by one

Update [terminalsetting] SET [SerialNumber] ='766' WHERE [TerminalID] ='63872'
Update [terminalsetting] SET [SerialNumber] ='215728108' WHERE [TerminalID] ='463328'
Update [terminalsetting] SET [SerialNumber] ='213808561' WHERE [TerminalID] ='168734'
Sam
  • 43
  • 2
  • 10
  • You could create a temporary table / cte / values clause with the TerminalId and Serial number you want and update using a join – Zohar Peled Mar 29 '17 at 18:49
  • 1
    Please provide sample data and explain how the value for `SerialNumber` is determined. – Gordon Linoff Mar 29 '17 at 19:08
  • Possible duplicate of [How to UPDATE from a SELECT in SQL Server?](http://stackoverflow.com/questions/2334712/how-to-update-from-a-select-in-sql-server) – Anand Mar 29 '17 at 19:10
  • I've added part of the 4000k Terminal IDs I need to update and what will be the serial number . So far the only thing I can think is the code I added at the end – Sam Mar 29 '17 at 21:23

1 Answers1

0

I would say use rownumber column and CTE. This will give you a unique number for each row. Then using this number and update your table.

Read this https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

Amit Uppal
  • 163
  • 1
  • 2
  • 7