updating table rows in postgres using subquery

Question

Using postgres 8.4, My goal is to update existing table:

CREATE TABLE public.dummy
(
  address_id SERIAL,
  addr1 character(40),
  addr2 character(40),
  city character(25),
  state character(2),
  zip character(5),
  customer boolean,
  supplier boolean,
  partner boolean

)
WITH (
  OIDS=FALSE
);

Initially i tested my query using insert statement:

insert into address customer,supplier,partner
SELECT  
    case when cust.addr1 is not null then TRUE else FALSE end customer, 
    case when suppl.addr1 is not null then TRUE else FALSE end supplier,
    case when partn.addr1 is not null then TRUE else FALSE end partner
from (
    SELECT *
        from address) pa
    left outer join cust_original cust
        on (pa.addr1=cust.addr1 and pa.addr2=cust.addr2 and pa.city=cust.city 
            and pa.state=cust.state and substring(cust.zip,1,5) = pa.zip  )
    left outer join supp_original suppl 
        on (pa.addr1=suppl.addr1 and pa.addr2=suppl.addr2 and pa.city=suppl.city 
                and pa.state=suppl.state and pa.zip = substring(suppl.zip,1,5))
    left outer join partner_original partn
        on (pa.addr1=partn.addr1 and pa.addr2=partn.addr2 and pa.city=partn.city
                  and pa.state=partn.state and pa.zip = substring(partn.zip,1,5) )
where pa.address_id = address_id

being Newbie I'm failing converting to update statement ie., updating existing rows with values returned by select statement. Any help is highly appreciated.

Solution 1

Postgres allows:

UPDATE dummy
SET customer=subquery.customer,
    address=subquery.address,
    partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
      FROM  /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;

This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.

Solution 2

You're after the UPDATE FROM syntax.

UPDATE 
  table T1  
SET 
  column1 = T2.column1 
FROM 
  table T2 
  INNER JOIN table T3 USING (column2) 
WHERE 
  T1.column2 = T2.column2;

References

• Code sample here: GROUP BY in UPDATE FROM clause
• And here
• Formal Syntax Specification

Solution 3

If there are no performance gains using a join, then I prefer Common Table Expressions (CTEs) for readability:

WITH subquery AS (
    SELECT address_id, customer, address, partn
    FROM  /* big hairy SQL */ ...
)
UPDATE dummy
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE dummy.address_id = subquery.address_id;

IMHO a bit more modern.

Solution 4

There are many ways to update the rows.

When it comes to UPDATE the rows using subqueries, you can use any of these approaches.

1. Approach-1 [Using direct table reference]

UPDATE
  <table1>
SET
  customer=<table2>.customer,
  address=<table2>.address,
  partn=<table2>.partn
FROM
  <table2>
WHERE
  <table1>.address_id=<table2>.address_i;

Explanation: table1 is the table which we want to update, table2 is the table, from which we'll get the value to be replaced/updated. We are using FROM clause, to fetch the table2's data. WHERE clause will help to set the proper data mapping.

2. Approach-2 [Using SubQueries]

UPDATE
  <table1>
SET
  customer=subquery.customer,
  address=subquery.address,
  partn=subquery.partn
FROM
  (
    SELECT
      address_id, customer, address, partn
    FROM  /* big hairy SQL */ ...
  ) AS subquery
WHERE
  dummy.address_id=subquery.address_id;

Explanation: Here we are using subquerie inside the FROM clause, and giving an alias to it. So that it will act like the table.

3. Approach-3 [Using multiple Joined tables]

UPDATE
  <table1>
SET
  customer=<table2>.customer,
  address=<table2>.address,
  partn=<table2>.partn
FROM
  <table2> as t2
  JOIN <table3> as t3
  ON
    t2.id = t3.id
WHERE
  <table1>.address_id=<table2>.address_i;

Explanation: Sometimes we face the situation in that table join is so important to get proper data for the update. To do so, Postgres allows us to Join multiple tables inside the FROM clause.

4. Approach-4 [Using WITH statement]

• 4.1 [Using simple query]

WITH subquery AS (
    SELECT
      address_id,
      customer,
      address,
      partn
    FROM
      <table1>;
)
UPDATE <table-X>
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE <table-X>.address_id = subquery.address_id;

• 4.2 [Using query with complex JOIN]

WITH subquery AS (
    SELECT address_id, customer, address, partn
    FROM
      <table1> as t1
    JOIN
      <table2> as t2
    ON
      t1.id = t2.id;
    -- You can build as COMPLEX as this query as per your need.
)
UPDATE <table-X>
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE <table-X>.address_id = subquery.address_id;

Explanation: From Postgres 9.1, this(WITH) concept has been introduced. Using that we can make any complex queries and generate desired result. Here we are using this approach to update the table.

I hope, this would be helpful..😊

Solution 5

@Mayur "4.2 [Using query with complex JOIN]" with Common Table Expressions (CTEs) did the trick for me.

WITH cte AS (
SELECT e.id, e.postcode
FROM employees e
LEFT JOIN locations lc ON lc.postcode=cte.postcode
WHERE e.id=1
)
UPDATE employee_location SET lat=lc.lat, longitude=lc.longi
FROM cte
WHERE employee_location.id=cte.id;

Hope this helps... :D

View more solutions

Comments

[stackover]

it seems that i'm trying for a bit different thing for eg. if there are 3 bool columns c1,c2,c3 all set to false initially. but based on subquery are set to true. update set c1=TRUE where id in (subquery1),set c2=TRUE where id in (subquery2), set c3=True where id in (subquery3). I was successful when i do split this as 3 updates but i'm not sure how to attain the result with a single update. hope this make sense.

[gsiems]

FWIW, Oracle does accept that basic construct, however the performance of the update tends to degrade severely as the tables get larger. That's o.k. though as Oracle also supports the MERGE statement.

[user9645]

This totally does not work in postgresql 9.5, I get ERROR: 42P01: relation "dummy" does not exist

[Andrew Lazarus]

dummy has to be replaced by the name of the table you are trying to update. Please understand question and answer before trying to apply.

[user9645]

Yes, sorry about that. My bad. Did not notice the original question was using table named "dummy"

[OJVM]

It may be worth to mention that in the beginning of the query it is not necessary to specify the path to the column of the left side, only at the end, otherwise the db will complain with ERROR: column reference "address_id" is ambiguous

[Hansang]

The syntax isn't compatible with older versions of Postgres, before v9.1, (see postgresql.org/docs/9.1/static/sql-update.html and the previous versions) I'm on v8.2, so you have to put the entire CTE/With statement inside brackets after the FROM keyword and it will work.

[juanm55]

Nicely put @Mayur thanks for taking the time to compile all of the answers in one. I personally go with number 4, because to me it's the most readable one (via separating both queries) and the most flexible since you can put whatever you need inside the WITH

[Mayur]

Glad to know that My answers are helpful. Keep Coding :)

[Y Y]

This is what I looked everywhere for. Thanks!

[newman]

Thanks for putting up all the options. How about the performance? Are they about the same?

[Andrew Lazarus]

Without wanting to take anything away from my top-rated answer across Stack Exchange, this syntax, which did not exist when my answer was written, is a little easier to read.

[Andrew Lazarus]

Unless the standard has changed, none of these options are the SQL standard. The FROM clause used to be an extension, while the standard required the obviously inferior UPDATE dummy SET col1 = (SELECT col1 FROM some_other table), col2= (SELECT col2 FROM some_other_table)—longer and repetitive.