Updating multiple rows using a FROM clause

by gorthx

Last week I discovered a new-to-me Postgres feature, which has been around for quite a while: you can use a FROM clause to UPDATE multiple rows based on values in another table. (See docs here and here. The second link is older but has a more illustrative example.)

After some recent large-scale changes to our network infrastructure, we had to update our inventory database to reflect which equipment had been upgraded. In the past, I’ve accomplished this by using perl to generate a series of individual statements like
UPDATE equipment
SET hostname = 'shiny', model = 'FancyCiscoRouter'
WHERE hostname = 'crufty';

into a file, and just running that with i. The not-so-new way is faster and easier.

I had my main table of equipment names:
foo=# SELECT * from equipment;
ip | hostname | model
----------+--------------------+-------
10.1.1.1 | old-and-in-the-way | 12000
10.2.1.1 | crusty | 7300
10.3.1.1 | decrepit | 7300

And a new table with the new equipment names & models:
foo=# SELECT * from new_equipment ;
ip | hostname | model
----------+----------+-------
10.1.1.1 | unicorn | ASR
10.2.1.1 | rainbow | 2900
10.3.1.1 | glitter | 2900

Here’s how you’d update the main table from the information in the new table:
BEGIN;
UPDATE equipment
SET hostname=new_equipment.hostname, model = new_equipment.model
FROM new_equipment
WHERE equipment.ip = new_equipment.ip;

Voila:
foo=# SELECT * FROM equipment ;
ip | hostname | model
----------+----------+-------
10.1.1.1 | unicorn | ASR
10.2.1.1 | rainbow | 2900
10.3.1.1 | glitter | 2900

…don’t forget to COMMIT. :)



Is anybody besides me super-annoyed that Cisco gave a router platform the same number series as the old Catalyst switches? Talk about confusing. “2900s? I thought we replaced those?”

Advertisements
Tags: ,
%d bloggers like this: