UPDATE with JOIN syntax by database: (1) PostgreSQL: UPDATE table SET col = val FROM source WHERE condition - FROM clause for JOIN. (2) MySQL: UPDATE table JOIN source ON condition SET col = val - JOIN directly after UPDATE. (3) SQL Server: UPDATE t SET col = val FROM table t JOIN source ON condition - similar to PostgreSQL. (4) Correlated subquery: UPDATE table SET col = (SELECT val FROM source WHERE condition) WHERE EXISTS - portable across all databases. (5) Batch pattern: UPDATE with WHERE id >= start AND id < end - process in chunks.

PostgreSQL: UPDATE table SET col = val FROM source WHERE condition - FROM clause for JOIN

MySQL: UPDATE table JOIN source ON condition SET col = val - JOIN directly after UPDATE

SQL Server: UPDATE t SET col = val FROM table t JOIN source ON condition - similar to PostgreSQL

Oracle: Use MERGE or correlated subquery - no UPDATE...JOIN syntax

Correlated subquery: UPDATE table SET col = (SELECT val FROM source WHERE condition) - portable across all databases

Always use WHERE clause: Limits rows updated, prevents accidental full table update

Index join columns: Foreign keys and primary keys must be indexed for performance

Batch large updates: Process 1000-5000 rows per transaction to avoid long locks

UPDATE...FROM syntax, supports RETURNING clause, excellent optimizer

UPDATE...JOIN syntax, supports LIMIT for batching, good optimizer

UPDATE...FROM syntax, supports OUTPUT clause, excellent optimizer, use WITH (TABLOCK) for large updates

Use MERGE or correlated subquery, no UPDATE...JOIN, use ROWNUM for batching

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

BETWEEN

Checks whether a value falls inside an inclusive lower/upper range.

order_total BETWEEN 100 AND 500

EXISTS

Tests whether a correlated or non-correlated subquery returns at least one row.

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

TIME

Stores a time-of-day value without any date component.

TIME '14:30:00'

TIMESTAMP

Stores date and time together, typically without timezone context.

TIMESTAMP '2026-04-18 14:30:00'