postgres tutorial 5 delete/update join, upsert

delete data of a table based on values in another table

DELETE FROM table
USING another_table
WHERE table.id = another_table.id AND …

DELETE FROM link
USING link_tmp
WHERE
    link.id = link_tmp.id;

=

DELETE FROM table
WHERE table.id = (SELECT id FROM another_table);

--------------------------
UPDATE join - update data of a table based on values in another table

UPDATE A
SET A.c1 = expresion
FROM B
WHERE A.c2 = B.c2;

UPDATE product
SET net_price = price - price * discount
FROM
product_segment
WHERE
product.segment_id = product_segment.id;

------------------------------------
upsert -  to insert or update data if the row that is being inserted already exists in the table.

INSERT INTO table_name(column_list) VALUES(value_list)
ON CONFLICT target action;

#SELECT * FROM customers;
 customer_id |   name    |         email         | active
-------------+-----------+-----------------------+--------
           1 | IBM       | contact@ibm.com       | t
           2 | Microsoft | contact@microsoft.com | t
           3 | Intel     | contact@intel.com     | t
(3 rows)

INSERT INTO customers (name, email)
VALUES
 (
 'Microsoft',
 'hotline@microsoft.com'
 )
ON CONFLICT (name)
DO NOTHING;

 concatenate the new email with the old email when inserting a customer that already exists,

INSERT INTO customers (name, email)
VALUES
 (
 'Microsoft',
 'hotline@microsoft.com'
 )
ON CONFLICT (name)
DO
 UPDATE
   SET email = EXCLUDED.email || ';' || customers.email;

#SELECT * FROM customers;
 customer_id |   name    |                    email                    | active
-------------+-----------+---------------------------------------------+--------
           1 | IBM       | contact@ibm.com                             | t
           3 | Intel     | contact@intel.com                           | t
           2 | Microsoft | hotline@microsoft.com;contact@microsoft.com | t
(3 rows)