Blip Blip - 5 months ago 32
SQL Question

postgresql update multiple tables in single query

I have 2 tables as below:


  1. serial_table

    id CHARACTER VARYING(20),
    serial_key CHARACTER VARYING(20),
    PRIMARY KEY(id, serial_key)

  2. serial_rate:

    id CHARACTER VARYING(20),
    serial_key CHARACTER VARYING(20),
    rate NUMERIC,
    PRIMARY KEY(id, serial_key),
    FOREIGN KEY (id, serial_key) REFERENCES serial_table(id, serial_key)



now I want to update
serial_rate.rate
and
serial_table.serial_key
from a single SQL Query like :

UPDATE inventory.serial_table AS s
JOIN inventory.serial_rate AS r
ON (s.id, s.serial_key) = (r.id, r.serial_key)
SET s.serial_key = '0002', r.rate = 22.53
WHERE (s.id, s.serial_key) = ('01', '002');


Which I know is incorrect. Is there a possible way to do this as I would like to use the statement to create a
PreparedStatement
in Java?

EDIT
This question is not about
PreparedStatements
in Java It is about SQL Syntax that I wish to pass as parameter while creating a
PreparedStatement
. I don't want any answer about `PreparedStatement.

Answer

This is a CTE thing (but I don't know how to wrap it into a prepared Java-thing)

WITH src AS (
        UPDATE serial_rate
        SET rate = 22.53, serial_key = '0002'
        WHERE serial_key = '002' AND id = '01'
        RETURNING *
        )
UPDATE serial_table dst
SET serial_key = src.serial_key
FROM src
-- WHERE dst.id = src.id AND dst.serial_key  = '002'
WHERE dst.id = '01' AND dst.serial_key  = '002'
        ;