How can I update all records in an Inventory Table to add "/invent" to the beginning of an-CodePudding

I want to add "/invent" at the beginning of the file path in the invImage and invThumbnail columns.

This is what I have in mind,

SELECT FROM inventory 
SELECT CONCAT("/invent")
AS invImage, invThumbnail;

Since it is not easy to undo mistakes in SQL, a confirmation of my potential solution will be helpful.

CodePudding user response：

Your current version will overwrite the value in the invImage column, but your stated goal is to

a) prepend the string to the existing value, and

b) do this in two columns, not just one

WHERE invImage = "/images"; also won't match any of the rows, because none of them contain exactly that value. I'll assume you want to update all rows whose invImage value starts with "/images".

Therefore, try this:

UPDATE 
  inventory 
SET 
  invImage = CONCAT("/invent", invImage),
  invThumbnail = CONCAT("/invent", invThumbnail),
WHERE 
  invImage LIKE "/images%";

CodePudding user response：

My suggestion: add temporarily two columns to the table: tmp_invImage and tmp_invThumbnail.

Then run the query:

UPDATE table SET 
tmp_invImage = CONCAT("/invent",invImage),
tmp_invThumbnail = CONCAT("/invent",invThumbnail)

After the update, look if the values of tmp_invImage and tmp_invThumbnail are correct.

Then update the original columns:

UPDATE table SET 
invImage = tmp_invImage),
invThumbnail = tmp_invThumbnail

and delete the tmp_ columns.