1

I'm trying to write an update query for 2 tables, but I'm facing an error, can you help?

I'm using postgresql I'm using sqlc with golang.

UPDATE categories 
SET c.slug = $1, tx.name = $2, tx.description = $3 
FROM categories c
JOIN categories_translations tx ON c.id = tx.category_id 
WHERE c.id = $4 AND tx.category_id = $4 AND tx.language_code = $5;
MeanwhileInHell
  • 6,780
  • 17
  • 57
  • 106
Developer
  • 21
  • 3
  • You haven't specified what error you're getting, but know this, you cannot update more than one table with a single `UPDATE`, regardless of how many joins you perform. Also note that you the columns you `SET` MUST be unqualified, i.e. `c.slug` is invalid, `slug` is valid. – mkopriva Jun 22 '22 at 10:46
  • The only way to update multiple tables executing a single statement would be to use CTEs. CTEs allow you to combine/chain multiple statements, including UPDATEs. – mkopriva Jun 22 '22 at 10:56
  • Can you share your solution @mkopriva? I'm genuinely curious. – lemon Jun 22 '22 at 11:00
  • 1
    @lemon here's an example: https://stackoverflow.com/questions/29898244/postgresql-update-multiple-tables-in-single-query – mkopriva Jun 22 '22 at 11:00

1 Answers1

3

Syntax of PostgreSQL UPDATE statement with a JOIN does not use the JOIN keyword. Try with the following:

UPDATE categories c
SET slug = $1 
WHERE c.id = $4;

UPDATE categories_translations tx
SET name = $2, 
    description = $3
WHERE tx.category_id = $4 
  AND tx.language_code = $5;

If you want to make a single query, you can use a cte, as suggested by mkopriva in the comments:

WITH cte AS (
    UPDATE categories c
    SET slug = $1 
    WHERE c.id = $4
)
UPDATE categories_translations tx
SET name = $2, 
    description = $3
WHERE tx.category_id = $4 
  AND tx.language_code = $5;

Note: You can't update values from multiple tables with only one UPDATE statement.

mkopriva
  • 35,176
  • 4
  • 57
  • 71
lemon
  • 14,875
  • 6
  • 18
  • 38
  • Error: column "c" does not exist – Developer Jun 22 '22 at 10:49
  • You're right mkopriva. Check the updated answer @Developer – lemon Jun 22 '22 at 10:58
  • Note that the FROMs are unnecessary. And here's an example that uses CTE: https://pastebin.com/pMGPvXSd – mkopriva Jun 22 '22 at 11:36
  • Does the second update inherit the conditions from the update in the cte? @mkopriva – lemon Jun 22 '22 at 11:53
  • Nope it doesn't inherit anything, but it has access to the `x` declared with `WITH`, and the `x` holds the set of rows returned by the `RETURNING` clause of the first update. But since the second update doesn't need that information it doesn't use it, it's just ignored. – mkopriva Jun 22 '22 at 11:56
  • And in case it's not obvious, the FROMs are unnecessary with or without the CTE. If you read the `WHERE` clauses in your own answer you can see that the parameters placeholders are intended to hold all the necessary identifiers required to match the rows that need to be updated. Basically there's no need for a join here. A join would only be useful if it was possible to update the two tables with a single UPDATE statement. – mkopriva Jun 22 '22 at 11:59
  • Though if we're not matching tab1.id and tab2.id, wouldn't there be the risk of updating rows whose id is present only in one table and not in the other? I mean, isn't the join condition required to make rows whose id is present in both tables to be updated? – lemon Jun 22 '22 at 12:06
  • 1
    That's what the `$4` is for. Look in your code, in both queries you're doing `c.id = tx.category_id AND c.id = $4 AND tx.category_id = $4` (btw the first and second conditionals make the third unnecessary). Now, can `id` and `category_id` be something else than `$4` and still result in a match? No they cannot. So doing just `category_id = $4` in the first update and `id = $4` in the second is completely sufficient. – mkopriva Jun 22 '22 at 12:13
  • I guess just didn't notice that there was already the join conditions right inside the where clause. Also reasoning on this kind of problem would have been much easier with some sample data. Thanks for the tips! – lemon Jun 22 '22 at 12:18