CODEWITHSUNDEEP
sqlsql-server
mortenstarck
mortenstarck

Reputation: 2803

Mass SQL Update of single table

I have an issue with doing an mass update of 35050 rows in a table, I need to update all barcodes which does not have an zero in front. The SQL SELECT statement I have is like so. It works as it should, but my update statement does not.

SELECT * 
FROM [Hep_ManEquipInterface].[dbo].[LabelUnitBarcodes] h WITH (NOLOCK) 
WHERE LEFT(h.barcode, 1) != '0'
ORDER BY [created_at] desc

Which give me this table.

barcode         weight  created_at                location  SupplierType
409101685473    2285    2015-02-02 12:23:03.830   372   
409101275155    2285    2015-02-02 12:22:51.143   372   
409101685466    2285    2015-02-02 12:22:34.983   372   
409101275148    2285    2015-02-02 12:22:25.280   372

Result should be, that the barcode has an zero in front:

barcode         weight  created_at                location  SupplierType
0409101685473   2285    2015-02-02 12:23:03.830   372   
0409101275155   2285    2015-02-02 12:22:51.143   372   
0409101685466   2285    2015-02-02 12:22:34.983   372   
0409101275148   2285    2015-02-02 12:22:25.280   372

I have off course tried but i'm keep getting an error then using this statement:

UPDATE [Hep_ManEquipInterface].[dbo].[LabelUnitBarcodes]
SET [barcode] = '0'+ (SELECT * h.[barcode] FROM [Hep_ManEquipInterface].[dbo].[LabelUnitBarcodes] h WITH (NOLOCK) WHERE LEFT(h.barcode, 1) != '0')

Upvotes: 0

Views: 87

Answers (1)

jarlh
jarlh

Reputation: 44766

Don't complicate things, it's just a simple UPDATE.

UPDATE [Hep_ManEquipInterface].[dbo].[LabelUnitBarcodes]
SET [barcode] = '0'+ [barcode]
WHERE LEFT(barcode, 1) != '0'

Upvotes: 5

Related Questions