Divyesh Jesadiya Divyesh Jesadiya - 11 months ago 38
SQL Question

how to update database from query

in my database i have many column like this.

Vendor VZWBuildingNumber VendorSiteID
ADO.NET 0 ACD-MI-12503
ADO.NET 0 ACD-MI-12518
ADO.NET 0 ACD-MI-12517

and i want it like this

Vendor VZWBuildingNumber VendorSiteID
ADO.NET 012503 ACD-MI-12503
ADO.NET 012518 ACD-MI-12518
ADO.NET 012517 ACD-MI-12517

i want last 5 letter of VendorSiteID and it apper in VZWBuildingNumber where vendor = ADO.NET and VZWBuildingNumber = 0. I tried but not any success Please help me with Query.


Mysql's SUBSTRING function is what you are looking for.

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax

Thus you can write your query as

UPDATE VZWBuildingNumber 
SET VZWBuildingNumber = CONCAT('0',SUBSTRING(VendorSiteID, 8, 7))
WHERE Vendor = 'ADO.NET'