CODEWITHSUNDEEP
sqlpostgresql
Fredrik
Fredrik

Reputation: 4171

Refactoring PostgreSQL WITH query with many update statements

I have a string of SQL queries that need to be executed in order, and each require a WITH query. Something like this:

WITH diff AS (
  SELECT NOW() - created_at as time_diff FROM table_a ORDER BY created_at DESC LIMIT 1
)
UPDATE table_x SET created_at = created_at + (SELECT time_diff FROM diff);

WITH diff AS (
  SELECT NOW() - created_at as time_diff FROM table_a ORDER BY created_at DESC LIMIT 1
)
UPDATE table_y SET created_at = created_at + (SELECT time_diff FROM diff);

WITH diff AS (
  SELECT NOW() - created_at as time_diff FROM table_a ORDER BY created_at DESC LIMIT 1
)
UPDATE table_a SET created_at = created_at + (SELECT time_diff FROM diff);

This is a slightly contrived example, but the intention is to "shift" the created_at timestamp of all records of a certain number of tables. Because table_a is included in all the WITH queries, it needs to be the last table to be updated or everything else would be wrong.

Ideally I'd just want to do something like this:

WITH diff AS (
  SELECT NOW() - created_at as time_diff FROM table_a ORDER BY created_at DESC LIMIT 1
)
UPDATE table_x SET created_at = created_at + (SELECT time_diff FROM diff)
UPDATE table_y SET created_at = created_at + (SELECT time_diff FROM diff)
UPDATE table_a SET created_at = created_at + (SELECT time_diff FROM diff);

Both because it's more concise and because then the order wouldn't really matter. But this is obviously not legal syntax.

Is there a way to accomplish this in a single query?

Upvotes: 0

Views: 38

Answers (1)

user330315
user330315

Reputation:

You need to put each update (except the last one) in a CTE:

WITH diff AS (
  SELECT NOW() - created_at as time_diff FROM table_a ORDER BY created_at DESC LIMIT 1
), upd_x as (
  UPDATE table_x SET created_at = created_at + (SELECT time_diff FROM diff)
), upd_y as (
  UPDATE table_y SET created_at = created_at + (SELECT time_diff FROM diff)
)
UPDATE table_a SET created_at = created_at + (SELECT time_diff FROM diff);

Upvotes: 2

Related Questions