postgresql update multiple tables in single query

Question

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 1

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'
        ;

Answer 2

This was what was posted by a_horse_with_no_name on sqlfiddle.com:

   WITH id_values (new_key, old_key, id) as (
      values ('0002', '002', '01')
    ), src AS (
      UPDATE serial_rate
          SET rate = 22.53, 
              serial_key = (select new_key from id_values)
      WHERE serial_key = (select old_key from id_values)
        AND id = (select id from id_values)
      RETURNING *
    )
    UPDATE serial_table dst
       SET serial_key = src.serial_key
    FROM src
    WHERE dst.id = (select id from id_values)
      AND dst.serial_key = (select old_key from id_values)
    ;

This is actually a modified version of what was posted by wildplasser as a reply to my query that whether the serial_key field could be used once.