Updating table rows in postgres using subquery

Question

Updating table rows in postgres using subquery

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

);

At first, I tried my query utilizing embed explanation:

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 to change over to update statement ie., update existing rows with values returned by the select statement. Any help is exceptionally valued.

postgresql sql

5 years ago by Kajalsi45d

3 Answers

Login / Signup to Answer the Question.

Login Signup

**akshay1995** · Answer 1 · 2021-06-22T12:36:02

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.

**sandhya6gczb** · Answer 2 · 2021-06-23T09:16:10

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;

**pankajshivnani123** · Answer 3 · 2021-06-28T14:24:23

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;

C TUTORIAL

C PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

C++ TUTORIAL

C++ PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

PYTHON TUTORIAL

PYTHON HOW TOS

INTERVIEW TESTS

EXECUTE CODE

JAVA TUTORIAL

JAVA CODE EXAMPLES

SPRING TUTORIAL

MORE IN JAVA

COMPUTER ARCHITECTURE

COMPUTER NETWORK

OPERATING SYSTEM

DBMS & SQL

PL/SQL

MongoDB

EXECUTE SQL

ANDROID DEVELOPMENT

GO LANGUAGE

LINUX

DOCKER

HTML TAGS (A to Z)

CSS REFERENCES

SASS/SCSS

KOTLIN

GAME DEVELOPMENT

PHP

GIT GUIDE

JAVASCRIPT

ADVANCED DSA

Updating table rows in postgres using subquery

3 Answers

Login / Signup to Answer the Question.