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

User · Answer

There are many ways to update the rows   When it comes to UPDATE the rows using subqueries  you can use any of these approaches    Approach-1  Using direct table reference    UPDATE    lt table1 gt  SET   customer  lt table2 gt  customer    address  lt table2 gt  address    partn  lt table2 gt  partn FROM    lt table2 gt  WHERE    lt table1 gt  address id  lt table2 gt  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     Approach-2  Using SubQueries    UPDATE    lt table1 gt  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     Approach-3  Using multiple Joined tables    UPDATE    lt table1 gt  SET   customer  lt table2 gt  customer    address  lt table2 gt  address    partn  lt table2 gt  partn FROM    lt table2 gt  as t2   JOIN  lt table3 gt  as t3   ON     t2 id   t3 id WHERE    lt table1 gt  address id  lt table2 gt  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     Approach-4  Using WITH statement    4 1  Using simple query     WITH subquery AS       SELECT       address id        customer        address        partn     FROM        lt table1 gt     UPDATE  lt table-X gt  SET customer   subquery customer      address    subquery address      partn      subquery partn FROM subquery WHERE  lt table-X gt  address id   subquery address id     4 2  Using query with complex JOIN    WITH subquery AS       SELECT address id  customer  address  partn     FROM        lt table1 gt  as t1     JOIN        lt table2 gt  as t2     ON       t1 id   t2 id      -- You can build as COMPLEX as this query as per your need    UPDATE  lt table-X gt  SET customer   subquery customer      address    subquery address      partn      subquery partn FROM subquery WHERE  lt table-X gt  address id   subquery address id       Explanation    From Postgres 9 1  this WITH  concept has been   introduces  Using that We can make any complex queries and generate   desire result    Here we are using this approach to update the table    I hope  this would be helpful

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] updating table rows in postgres using subquery

Examples related to sql

Examples related to postgresql

Examples related to subquery

Examples related to sql-update