Update and append to all items in a specific column-CodePudding

How do I append a character to all items `purchase_id' here is a manual example of what I want...

SELECT * 
FROM `loadable_link` 
WHERE `product_sku` = '2101-R' 
ORDER BY `customer_id` DESC

Then select from purchased_id and append a '0' to all purchased ID's

UPDATE `loadable_link` SET `purchased_id` = '11165690' 
WHERE `loadable_link`.`purchased_id` = 1116569;

CodePudding user response：

You can update the table according to the condition in the original select statement.

If purchase_id is a number, you can multiply it by 10:

UPDATE `loadable_link`
SET    `purchase_id` = `purchase_id` * 10
WHERE  `product_sku` = '2101-R'

If purchase_id is a string, you can concatenate a 0 to it:

UPDATE `loadable_link`
SET    `purchase_id` = CONCAT(`purchase_id`, '0')
WHERE  `product_sku` = '2101-R'

CodePudding user response：

UPDATE `loadable_link`
SET `purchased_id` = CONCAT(`purchased_id`, "0")
WHERE `product_sku` = "2101-R";

This can be achieved in one UPDATE query, take the original value of each row and CONCAT() to append a 0 to the end of the existing purchase_id.