Update statement using with clause

sql

prompt-engineering

best-practices

performance

byNikita Barsukov·Jan 10, 2025

Efficiently update your SQL table using a WITH clause with a Common Table Expression (CTE):

WITH UpdatedValues AS (
  -- Meet your new_value, freshly sourced! 
  SELECT id, new_value FROM source_table WHERE your_condition
)
UPDATE target_table
SET column_to_update = UpdatedValues.new_value
FROM UpdatedValues
-- Wish our ids match, like soulmates!
WHERE target_table.id = UpdatedValues.id;

Here, the CTE extracts the new_value, and the UPDATE statement applies it onto target_table where the id column matches.

Using nested CTEs for calculation power

Say, you have some heavy calculations or intermediate data transformations before an UPDATE. The trick is to nest your CTEs:

WITH Intermediate AS (
  -- Your magical computations!
),
UpdatedValues AS (
  -- Freshly baked results served!
  SELECT id, new_value FROM Intermediate
)
UPDATE target_table
SET column_to_update = UpdatedValues.new_value
FROM UpdatedValues
-- Bingo, we've found matching ids!
WHERE target_table.id = UpdatedValues.id;

By structuring your UPDATE with nested CTEs, you'll be dancing with your calculations while keeping those ugly nested subqueries at bay!

Merging for the win in advanced scenarios

Advanced scenarios need you to dynamically insert or update rows. Drum roll please - here's the MERGE INTO statement:

WITH SourceData AS (
  -- Grab your data from the source_table.
  SELECT id, new_value FROM source_table
)
MERGE INTO target_table USING SourceData
ON (target_table.id = SourceData.id)
WHEN MATCHED THEN
  UPDATE SET column_to_update = SourceData.new_value
WHEN NOT MATCHED THEN
  INSERT (id, column_to_update) VALUES (SourceData.id, SourceData.new_value);

MERGE INTO lets you marry INSERT and UPDATE. And the best part? It still looks good the morning after!

The oracle of Oracle's key preservation

For Oracle users, keep the keys! The WITH clause should go hand in hand with Oracle's key-preserved requirement:

WITH ValidPairs AS (
  -- Only the worthy ids shall pass.
  SELECT source.id, target.id AS target_id
  FROM source_table source
  JOIN target_table target
  ON source.foreign_key = target.primary_key
  WHERE source.condition = 'valid'
)
UPDATE target_table t
SET t.column_to_update = (
  SELECT new_value
  FROM source_table s
  JOIN ValidPairs vp ON s.id = vp.id
  WHERE vp.target_id = t.id
)
WHERE EXISTS (
  SELECT 1 FROM ValidPairs vp WHERE vp.target_id = t.id
);

No more clumsy errors like the dreaded ORA-01732, and you can run UPDATEs without kicking table constraints to the curb.

Dodge those common pitfalls

Complexity can be a sly villain. Overcomplicated CTEs can hit on performance, especially with bulky intermediate results. So keep an eye on any performance lags and consider racing with indexing or rewriting your queries to keep efficiency in check.

explain-codes / Sql / Update statement using with clause

Linked

Update records in table from CTE



Update multiple rows in same query using PostgreSQL



How to iterate over results of a query



Update statement with inner join on Oracle



Mysql, update multiple tables with one query

