How to update the field value of specific's column in SQL Server using Query in one shot?

Question

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.

Image

Answer 1

UPDATE a
SET a.Imagepath = a.ImagePath + '.jpg'
FROM aspx_itemimages a
WHERE a.ItemId = yourid

Answer 2

Try this one -

UPDATE aspx_itemimages
SET ImagePath = ImagePath + '.jpg'
--WHERE itemid = 755

Answer 3

Try:

UPDATE ImagePath SET ImagePath = ImagePath + '.jpg'

Answer 4

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.

Answer 5

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