CODEWITHSUNDEEP
sqlpostgresql
Larrrrrrrrrry
Larrrrrrrrrry

Reputation: 163

PostgreSQL SELECT, UPDATE and INSERT together

I'm currently trying to SELECT columns from a table, do some arithmetic with the selected values and then INSERT the updated values back into the same table. So far, I'm thinking of SELECT all columns, copy to another table, UPDATE that table and copy it back but this seems kinda redundant.

INSERT INTO tableB (x, y) SELECT x, y FROM tableA;

UPDATE tableB SET y = y + 1;

INSERT INTO tableA (x, y) SELECT x, y FROM tableB;

where x and y are declared UNIQUE(x, y)

EDIT: Is there a way to avoid creating another table for this transaction?

Upvotes: 1

Views: 1856

Answers (2)

user330315
user330315

Reputation:

If you want to copy the changed data, you can do that with a single insert:

insert into tablea (x,y)
select x, y + 1
from tablea;

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

If I read your logic correctly, then you only need two inserts here, possibly inside a transaction:

BEGIN;
    INSERT INTO tableB (x, y) SELECT x, y+1 FROM tableA;
    INSERT INTO tableA (x, y) SELECT x, y FROM tableB
COMMIT;

The update after the insert is unnecessary as you can simply insert the incremented values of y.

Upvotes: 1

Related Questions