Update or Insert (Multiple Rows and Columns) from Subquery in Postgresql

Update or Insert (multiple rows and columns) from subquery in PostgreSQL

For the UPDATE

Use:

UPDATE table1 
   SET col1 = othertable.col2,
       col2 = othertable.col3 
  FROM othertable 
 WHERE othertable.col1 = 123;

For the INSERT

Use:

INSERT INTO table1 (col1, col2) 
SELECT col1, col2 
  FROM othertable

You don't need the VALUES syntax if you are using a SELECT to populate the INSERT values.

Insert into Multiple Columns from a Grouped by Subquery

try like below if want to use subsqery

INSERT INTO TABLE (COLUMN_A, COLUMN_B, COLUMN_C)              -
SELECT 'Label A', CATEGORY,  cnt from                                          
(SELECT CATEGORY, COUNT(1) as cnt FROM SUB_TABLE GROUP BY CATEGORY) a

in fact don't need sub-query

 INSERT INTO TABLE (COLUMN_A, COLUMN_B, COLUMN_C)              -
SELECT 'Label A', CATEGORY,  COUNT(1) as cnt from                                          
  FROM SUB_TABLE GROUP BY CATEGORY

demo link

How to PERFORM a CTE query returning multiple rows/columns?

The WITH query enclosed in parentheses is treated like a sub-select. It works fine the way you have it as long as it returns a single value (one column of one row). Else you must treat it as subquery and call it like this (inside a PL/pgSQL code block!):

PERFORM * FROM (with test_as_cte as (select * from b2) select * from test_as_cte t) sub;

Or just:

PERFORM FROM (<any SELECT query>) sub;

The manual:

PERFORM query;

This executes query and discards the result. Write the query
the same way you would write an SQL SELECT command, but replace the
initial keyword SELECT with PERFORM. For WITH queries, use
PERFORM and then place the query in parentheses. (In this case, the
query can only return one row.)

I think this could be clearer. I'll suggest a fix for the documentation.

Insert multiple data from subquery into another table in postgres sql

The INSERT INTO ... SELECT syntax does not use a VALUES clause. Fix your syntax slightly, and your query should work:

INSERT INTO alpha (school_name)
SELECT school_name
FROM beta;

Update multiple rows in same query using PostgreSQL

You can also use update ... from syntax and use a mapping table. If you want to update more than one column, it's much more generalizable:

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

You can add as many columns as you like:

update test as t set
    column_a = c.column_a,
    column_c = c.column_c
from (values
    ('123', 1, '---'),
    ('345', 2, '+++')  
) as c(column_b, column_a, column_c) 
where c.column_b = t.column_b;

sql fiddle demo

Update multiple columns from a sub query

update PINPOINT_SUPPLEMENT
set
    ATTACHMENT_VALUE = PINPOINT_DOCUMENT.key,
    ATTACHMENT_TYPE = 'file'
from PINPOINT_DOCUMENT
where
    PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE::integer = PINPOINT_DOCUMENT.DOCUMENT_ID
    and PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE IS NULL

or

update PINPOINT_SUPPLEMENT
set
    (ATTACHMENT_VALUE,ATTACHMENT_TYPE) = (PINPOINT_DOCUMENT.key, 'file')
from PINPOINT_DOCUMENT
where
    PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE::integer = PINPOINT_DOCUMENT.DOCUMENT_ID
    and PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE IS NULL

updating table rows in postgres using subquery

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.

---

Related Topics