Update PostgreSQL tables with one SQL query

Question

I have these two tables which I would like to update with one SQL query using Java code:

CREATE TABLE ACCOUNT(
 ID INTEGER NOT NULL,
 USER_NAME TEXT NOT NULL,
 PASSWD TEXT,
 FIRST_NAME TEXT,
 LAST_NAME TEXT,
 E_MAIL TEXT NOT NULL,
 COUNTRY TEXT,
 STATE TEXT,
 LAST_PASSWD_RESET DATE,
 DESCRIPTION TEXT,
 LAST_UPDATED DATE,
 CREATED DATE
)
;

-- ADD KEYS FOR TABLE ACCOUNT

ALTER TABLE ACCOUNT ADD CONSTRAINT KEY1 PRIMARY KEY (ID)
;

ALTER TABLE ACCOUNT ADD CONSTRAINT USER_NAME UNIQUE (USER_NAME)
;

ALTER TABLE ACCOUNT ADD CONSTRAINT E_MAIL UNIQUE (E_MAIL)
;

-- TABLE ACCOUNT_ROLE

CREATE TABLE ACCOUNT_ROLE(
 ID INTEGER NOT NULL,
 USER_NAME TEXT NOT NULL,
 ROLE INTEGER,
 PERMISSION TEXT,
 LAST_UPDATED DATE,
 CREATED DATE
)
;

-- CREATE INDEXES FOR TABLE ACCOUNT_ROLE

CREATE INDEX IX_RELATIONSHIP19 ON ACCOUNT_ROLE (ID)
;

-- ADD KEYS FOR TABLE ACCOUNT_ROLE

ALTER TABLE ACCOUNT_ROLE ADD CONSTRAINT KEY26 PRIMARY KEY (ID)
;

ALTER TABLE ACCOUNT_ROLE ADD CONSTRAINT RELATIONSHIP19 FOREIGN KEY (ID) REFERENCES ACCOUNT (ID) ON DELETE CASCADE ON UPDATE CASCADE
;

I use this SQL query to update first table data. For the second table I use similar SQL query:

UPDATE ACCOUNT SET ID = ?, USER_NAME = ?, PASSWD = ?, FIRST_NAME = ?, LAST_NAME = ?, E_MAIL = ?, COUNTRY = ?, STATE = ?, " CITY = ?, ADDRESS = ?, STATUS = ?, SECURITY_QUESTION = ?, SECURITY_ANSWER = ?, DESCRIPTION = ?, LAST_UPDATED = CURRENT_DATE WHERE ID = ?

How this can solve this?

Solve what? You seem to understand how to write an update statement. — Gordon Linoff
– Gordon Linoff, Commented Jun 9, 2016 at 16:25
@PeterPenzov: why do you think you need to do that in a single statement? Why not simply run two update statements if you want to update two tables? — user330315
– user330315, Commented Jun 10, 2016 at 6:26
Because if one SQL statement fails I need to rowback both SQL inserts. — Peter Penzov
– Peter Penzov, Commented Jun 10, 2016 at 6:32

Gordon Linoff · Accepted Answer · 2016-06-10 01:46:30Z

1

In Postgres, you can update two tables by using CTEs:

with t1 as (
      update . . .
      returning *
     )
update t2
    . . .

It is unclear how this fits into what you are trying to do. But it is possible to write one statement in Postgres that does two updates.

answered Jun 10, 2016 at 1:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jerzyk · Accepted Answer · 2016-06-09 17:14:48Z

1

you can't unless you have two tables that use inheritance

I do not know any SQL DB that will allow changes to two or more tables at the same time.

Not sure if you will ever need such a functionality.

If you would like to keep changes together - just open a transaction and put those two updates together, if one will fail, whole transaction can be rolled-back.

Of course one can use explicit locking with SELECT .. FOR UPDATE which is fine too.

If you are looking something to simplify code - maybe you should look at stored procedures, so you will be able to write complex code and call it with some parameters.

answered Jun 9, 2016 at 17:14

Jerzyk

3,77227 silver badges41 bronze badges

1 Comment

user330315 Over a year ago

Postgres can update two tables in a single statement using data modifying CTEs: postgresql.org/docs/current/static/…

Collectives™ on Stack Overflow

Update PostgreSQL tables with one SQL query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related