Updating multiple values at the same time SQL-CodePudding

I am trying to update multiple values at the same time,

Table values

UniqueRef, Name
1101, AA01
1102, AA02
1103, AA03

I want to update UniqueRef for all of 1101, 1102 and 1103 to different values e.g 1101 will be updated to 1101AB ect

how can I do this in bulk than one at a time?

CodePudding user response：

Do it in a single UPDATE statement:

Seems like you need to update UniqueRef in a single pattern. So use following statement:

UPDATE YourTable SET name = CONCAT(UniqueRef, 'AB');

This will update all rows - in case you need to limit the scope use WHERE statement and define appropriate condition.

In case you need to use different values for each row use CASE statement in

UPDATE YourTable 
SET name = 
  CASE
    WHEN UniqueRef = 1101 THEN 'newValue' 
    WHEN UniqueRef = 1102 THEN 'anotherNewValue' 
    {nextStatements} 
    ELSE name
  END
;

CodePudding user response：

The scalable solution is to use UPDATE with a JOIN on a source for the new values (usually another table, but it can be an online view, such as below)

UPDATE
  example
INNER JOIN
(
  SELECT 1101 AS id, 'X' AS new_name
  UNION ALL
  SELECT 1102, 'Y'
)
  AS update_map
    ON update_map.id = example.uniqueRef
SET
  example.name = update_map.new_name

Demo : https://dbfiddle.uk/g71hwuYG