19

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.

Improve this question
5
  • Do you really intend to update serial_table.serial_key , a PK component? And: should serial_rate.serial_key also be updated (also a PK component, and FK to the first) ? If not: the FK constraint could be violated. Commented Apr 27, 2015 at 14:31
  • @wildplasser the first answer is yes and as for the second part the FK is ON UPDATE CASCADE ON DELETE RESTRICT. Commented Apr 27, 2015 at 14:34
  • possible duplicate of One preparedstatement for multiple tables Java Commented Apr 27, 2015 at 14:46
  • @JarrodRoberson I am unable to understand why you think that this question is similar to the one you have linked? I am not getting the answer to what i want in that link. Commented Apr 27, 2015 at 14:52
  • I would remove all the mentions of "prepared statement" if your question is only about SQL syntax. Commented Apr 28, 2015 at 8:08

2 Answers 2

Reset to default
26

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'
        ;
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

This is still two statements, it's not as immediately clear are just doing two update statements in a transaction/sproc, and will be more difficult to implement as a prepared statement (think a long ugly string with variables all over the place)
No, they are chained together (rather clumsily). Hint: there is only one semicolon. Just put EXPLAIN in front of it, and you'll see that it will result in one query plan for the combined updates.
It's effectively a subquery though right? It's two queries with one query plan?
@DanField: No, it's just one single query. And of course it has only one query plan.The trick is the combination of a CTE and the usage of RETURNING in the first UPDATE-command, that will be used in the second UPDATE-command.
@Blip: yes that's possible with another CTE in the query: sqlfiddle.com/#!15/75387/1
|
5

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.