CODEWITHSUNDEEP
sqlpostgresqlinsert-into
gruenschimmel
gruenschimmel

Reputation: 13

SQL insert into AND update

Is it possible in PostgreSQL to update a table and insert data into another table at the same time.

Like

UPDATE table1
SET column4=true
AND INSERT into table2
SELECT column1, column2, column3
FROM table 1
WHERE column1="peter"
;

Upvotes: 1

Views: 213

Answers (2)

GolezTrol
GolezTrol

Reputation: 116100

You can execute multiple statements inside a transaction, so it will still be an atomic change to your data.

BEGIN;

UPDATE table1
SET column4=true;

INSERT into table2
SELECT column1, column2, column3
FROM table 1
WHERE column1="peter";

COMMIT;

If for whatever reason the insert fails, the entire transaction will fail, and the changes will be backed out, including the update that was executed before the insert statement.

Upvotes: 0

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28511

On Postgres 9.1 or later you can use something like:

WITH source AS (UPDATE table1
                SET column4=true
                WHERE column1='peter'
                RETURNING column1, column2, column3)
INSERT INTO table2 
SELECT column1, column2, column3
FROM source;

Upvotes: 2

Related Questions