Question 113376 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I update all records in an Inventory Table to add "/invent" to the beginning of an existing file path using a single query?

enter image description here

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.

Tags:

2

Answers


  1. 0

    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%";
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search