How to update all rows in a column without manually entering in update statement for every row in SQL

Question

I have a table called equipment and column called equip_identifier. in equip_identifier there are rows of computer equipment data like this:

equip_identifier
COMP-1356G
COMP-2537G
COMP-6787G
COMP-2583G
COMP-8064G
COMP-7643G
COMP-9072G

I have to replace the numbers in each row with different values I was given. for example:

COMP-1356G needs to be changed to COMP-2200G

COMP-2537G needs to be changed to COMP-5881G

what is the most efficient way to update all of these values if I have 700+ rows to update?

is there a pattern to this number conversion? If not , the answer is to update them one by one — eshirvana, Jul 10 '23 at 18:06
Create a static table, from and to and join on `From` and update to `To` — Nathan_Sav, Jul 10 '23 at 18:07
Which dbms are you using? UPDATE FROM is a non-standard syntax. — jarlh, Jul 10 '23 at 18:27

score 0 · Answer 1 · answered Jul 10 '23 at 18:42

CREATE TABLE equipment (
    equip_identifier VARCHAR(100)
);


INSERT INTO equipment (equip_identifier)
VALUES
    ('COMP-1356G'),
    ('COMP-2537G'),
    ('COMP-6787G'),
    ('COMP-2583G'),
    ('COMP-8064G'),
    ('COMP-7643G'),
    ('COMP-9072G');




UPDATE equipment
SET equip_identifier = 
    CASE 
        WHEN equip_identifier = 'COMP-1356G' THEN 'COMP-2200G'
        WHEN equip_identifier = 'COMP-2537G' THEN 'COMP-5881G'
        -- Add more WHEN conditions for other replacements
        ELSE equip_identifier -- Keep the original value if no replacement is specified
END;

How to update all rows in a column without manually entering in update statement for every row in SQL

1 Answers1