CODEWITHSUNDEEP
sqlpostgresqljoinsql-update
ShooShoSha
ShooShoSha

Reputation: 974

How to update table from linking table?

I am using a PostgreSQL database and querying in the dark here. I have three (3) tables: zip_code, zip_code_type, and temp_zip_type. They have the following columns:

zip_code
  zip_5_digit text
  type_id     integer

zip_code_type
  id    integer
  value text

temp_zip_type
  temp_zip  text
  temp_type text

I am trying to UPDATE zip_code and SET zip_code.type_id = zip_code_type.id so that zip_code.zip_5_digit matches with temp_zip_type.temp_zip, and temp_zip_type.temp_type matches zip_code_type.value.

Here is my attempt:

UPDATE
    zip_code
SET
    type_id = 
    (
        SELECT
            id
        FROM
            zip_code_type
        JOIN
            temp_zip_type
        ON
            temp_type = value
        WHERE
            temp_zip = zip_5_digit
    );

The result I am looking for is the type_id match the value in zip_code_type.id by looking up the values across the tables.

Workaround

I altered temp_zip_type and added an integer column called temp_type_id. I got the results I wanted by executing the following queries:

UPDATE
    temp_zip_type
SET
    temp_type_id =
    (
        SELECT
            id
        FROM
            zip_code_type
        WHERE
            temp_type = value
    );


UPDATE
    zip_code
SET
    type_id =
    (
        SELECT
            temp_type_id
        FROM
            temp_zip_type
        WHERE
            zip_5_digit = temp_zip
    );

For future reference, is there a more efficient query? These queries took 147 seconds to complete.

Upvotes: 0

Views: 63

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657857

Use the FROM clause of UPDATE to join temp_zip_type and zip_code_type and do it all in one simple statement:

UPDATE zip_code z
SET    type_id = t.id
FROM   temp_zip_type tmp
JOIN   zip_code_type t ON t.value = tmp.temp_type
WHERE  z.zip_5_digit = tmp.temp_zip
AND    z.type_id IS DISTINCT FROM t.id;  -- avoid empty updates

I added the last line to avoid empty updates. It may or may not be useful. Details:

Upvotes: 1

Related Questions