Chaining multiple queries in SQL

Question

I'm using postgresql if that matters.

I need to write into 2 different tables when a new user is created and I want to make sure either both queries fail or both of them succeed. If one of them fails, the other should not succeed.

Lets say I've a table named users and another table named houses and whenever a user is created I give them a house.

user table:
userid(int,auto generated) , username(text), password(text)


houses table:
userid(int) , houseid(int)

when a new user is created, I should call following code:

INSERT INTO users (username , password ) VALUES ('a value', 'a value') RETURNING userid;

INSERT INTO houses (userid, houseid ) VALUES ('returned_value' , 'a value');

but if first query succeeds and the second one fails for whatever reason, that means this will create a user without home. How can I chain these 2 queries together so either both of them succeeds or both of them fails.

You should wrap both insert statements in a single transaction. — Tim Biegeleisen, Sep 26 '21 at 09:54
@Christophe as I already stated in the question I'm using postgresql — ozan deniz, Sep 26 '21 at 09:57
@Tim Biegelisen can you give a simple example? that's what I'm confused about , I don't know how to wrap them. — ozan deniz, Sep 26 '21 at 09:58
Sorry I did not notice. I asked as the syntax differ from one to another — Christophe, Sep 26 '21 at 10:05
[https://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization](https://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization) refer this. — vivek pawar, Sep 26 '21 at 10:11

score 1 · Accepted Answer · answered Sep 26 '21 at 10:05

Transaction is the way you manage what you want to do here, I mean have one global commit only.
With postgresql, the syntax for managing transactions is the following:

BEGIN;
INSERT INTO users (username , password ) VALUES ('a value', 'a value') RETURNING userid;
INSERT INTO houses (userid, houseid ) VALUES ('returned_value' , 'a value');
COMMIT;

Chaining multiple queries in SQL

1 Answers1