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.

2

7 Answers

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.

6

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

0

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.

3

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, table2is the table, from which we'll get the value to be replaced/updated. We are using FROM clause, to fetch the table2's data. WHEREclause 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..😊

4

@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

update json_source_tabcol as d
set isnullable = a.is_Nullable
from information_schema.columns as a
where a.table_name =d.table_name
and a.table_schema = d.table_schema
and a.column_name = d.column_name;

For PostgreSQL check

UPDATE tableA SET (addr1, adrr2) = (SELECT addr1, addr2 FROM tableB WHERE tableA.id = tableB.tableA_id);

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy