SQL insert/update to a table from another table based on some condition

Question

I have a table like below named table1:

+-----------+-------+
| productid | stock |
+-----------+-------+
|         1 |    10 |
|         2 |    20 |
|         3 |    30 |
+-----------+-------+

I need to insert / update to the above table from another table named table2:

+-----+-------+
| PId | stock |
+-----+-------+
|   1 |    20 |
|   2 |    40 |
|   4 |    10 |
+-----+-------+

I would like to execute an SQL query with below condition:

1. if PId from table2 exist in Productid in table1 then need to update the value of stock.
2. if PId from table2 doesnt exist in Productid in table1 then need to insert the value of stock as a new row in table1 from table2.

So after executing query output in table1 would be like below:

+-----------+-------+
| productid | stock |
+-----------+-------+
|         1 |    20 |
|         2 |    40 |
|         3 |    30 |
|         4 |    10 |
+-----------+-------+

Help me to get the query as I am new to SQL. Thanks in advance!

Answer 1

You want a MERGE statement. Most database management systems support it nowadays:

MERGE INTO table1
USING table2
   ON table1.productid=table2.pid
WHEN MATCHED THEN UPDATE SET
  stock = table2.stock
WHEN NOT MATCHED THEN INSERT VALUES (
  table2.pid
, table2.stock
)
;

SELECT * FROM table1 ORDER BY 1;
-- out  OUTPUT 
-- out --------
-- out       3
-- out (1 row)
-- out 
-- out Time: First fetch (1 row): 27.090 ms. All rows formatted: 27.155 ms
-- out  productid | stock 
-- out -----------+-------
-- out          1 |    20
-- out          2 |    40
-- out          3 |    30
-- out          4 |    10