How to update the field value of specific’s column in SQL Server using Query in one shot?
I have Imported Data Form XLS my CMS. but finally realize that it has wrong data without any Extension at the end of the data, in One of the column of the table, so now I want to change the column data of multiple rows based on specific ID? How to do that using single SQL Query?
I just want to add ‘.jpg’ at the end on these data.
UPDATE a SET a.Imagepath = a.ImagePath + '.jpg' FROM aspx_itemimages a WHERE a.ItemId = yourid
Try this one –
UPDATE aspx_itemimages SET ImagePath = ImagePath + '.jpg' --WHERE itemid = 755
UPDATE ImagePath SET ImagePath = ImagePath + '.jpg'
According to your screenshot, some entries actually do end with
.jpg. If you want to append
.jpg only where it is absent, you could use a
NOT LIKE predicate:
UPDATE aspx_itemimages SET ImagePath = ImagePath + '.jpg' WHERE ImagePath NOT LIKE '%.jpg' ;
That will also prevent you from adding the extension multiple times if you run the query again accidentally.
The above answers are correct but adding 2 strings returns 0, you will have to concat two strings.
here is the example:
UPDATE a SET a.Imagepath = concat(ImagePath , '.jpg') FROM aspx_itemimages a WHERE a.ItemId = yourid
Best and Easiest way to do this…
"UPDATE aspx_itemimages SET itempath = itempath +'.jpg' WHERE itemid = 755 ";
Note: The Update doesn’t generate a result set. If you want to know which records will be modified, first run a
Select query that uses the same criteria. If the results are
satisfactory, then run the update query.