SQLite UPDATE FROM Alias Scope in RETURNING Clause: Troubleshooting “no such column” Error

Understanding Alias Visibility in SQLite UPDATE Queries with RETURNING Clauses

1. Core Mechanics of Aliases in UPDATE-FROM-RETURNING Workflows

SQLite’s UPDATE ... FROM ... RETURNING syntax introduces nuanced scoping rules for table aliases that diverge from PostgreSQL and other databases. When executing an UPDATE statement with a FROM clause, SQLite binds the target table’s alias (_new_ in this case) to the rows being modified. However, aliases defined in the FROM subquery (_old_ here) are not propagated to the RETURNING clause due to SQLite’s execution model. This creates a disconnect: while PostgreSQL allows RETURNING to reference aliases from both the target and FROM clauses, SQLite restricts RETURNING to columns directly tied to the updated rows or expressions derived from them.

The error no such column: _old_.name arises because SQLite’s parser evaluates the RETURNING clause in a scope where _old_ is undefined. This occurs even though _old_ is visible in the WHERE clause. SQLite processes the FROM subquery as a temporary dataset for matching rows but discards its alias context before reaching RETURNING. Consequently, attempts to access _old_.name fail because the alias _old_ exists only during the UPDATE’s row selection phase, not during result emission.

Key technical factors:

• Phase Separation: SQLite’s query execution separates row modification (via UPDATE ... FROM) from result generation (via RETURNING). Aliases in FROM are part of the row selection phase and are not retained for the result phase.
• Target-Centric Scope: The RETURNING clause only "sees" columns from the updated table (demo), its aliases (_new_), and literal expressions. Subquery aliases like _old_ are out of scope.
• PostgreSQL Compatibility Gap: PostgreSQL unifies the scope of FROM and RETURNING, allowing cross-alias references. SQLite’s implementation prioritizes simplicity over compatibility in this area.

2. Root Causes of Alias Reference Failures in RETURNING

Three primary factors explain why _old_.name is inaccessible:

A. Alias Lifetime Mismatch
Aliases defined in FROM subqueries are transient—they exist only during the UPDATE’s row identification phase. Once rows are selected for update, these aliases are discarded. The RETURNING clause operates on the post-update state of the table, where _old_ no longer exists. This contrasts with WHERE, which operates during row selection and retains access to _old_.

B. Column Resolution Rules
SQLite resolves column names in RETURNING using the following priority:

1. Columns of the updated table (or its alias).
2. Literal expressions.
3. Aggregates or window functions (if present).

Subquery aliases like _old_ are excluded from this resolution hierarchy. When parsing _old_.name, SQLite checks the updated table (demo) for a column named _old_.name, which doesn’t exist, triggering the error.

C. Lack of Correlation Between Phases
The FROM clause in an SQLite UPDATE acts as a row filter, not a data source for the RETURNING clause. While PostgreSQL allows RETURNING to reference data from joined tables, SQLite treats FROM as a filtering mechanism only. Thus, _old_ is not a correlated subquery whose values can be emitted post-update.

3. Resolving Alias Scope Issues and Emulating PostgreSQL Behavior

To achieve the desired result—returning both old and new values from an UPDATE—use these strategies:

A. Pre-Capture Old Values via CTE
Use a Common Table Expression (CTE) to store old values before performing the update:

WITH old_values AS (
  SELECT id, name FROM demo WHERE id = 3
)
UPDATE demo
SET name = 'Name 2'
WHERE id IN (SELECT id FROM old_values)
RETURNING (SELECT name FROM old_values) AS oldName, name AS newName;

This decouples the old value retrieval from the UPDATE, making old_values accessible in RETURNING.

B. Utilize Temporary Tables
For complex scenarios, store old values in a temporary table:

CREATE TEMP TABLE temp_old AS SELECT id, name FROM demo WHERE id = 3;
UPDATE demo
SET name = 'Name 2'
WHERE id IN (SELECT id FROM temp_old)
RETURNING (SELECT name FROM temp_old) AS oldName, name AS newName;
DROP TABLE temp_old;

C. Re-Query the Database
If transaction isolation permits, query the old values after the update:

UPDATE demo SET name = 'Name 2' WHERE id = 3 RETURNING name AS newName;
SELECT name AS oldName FROM demo_history WHERE id = 3;  -- Assuming a history table

D. Trigger-Based Archiving
Create a BEFORE UPDATE trigger to automatically capture old values:

CREATE TABLE demo_history (id INT, old_name TEXT, new_name TEXT);
CREATE TRIGGER log_demo_update BEFORE UPDATE ON demo
BEGIN
  INSERT INTO demo_history (id, old_name, new_name)
  VALUES (OLD.id, OLD.name, NEW.name);
END;

After the update, query demo_history to retrieve old and new values.

E. Direct Table References
When possible, avoid FROM aliases in RETURNING. Use the base table name instead:

UPDATE demo AS _new_
SET name = 'Name 2'
FROM (SELECT id FROM demo AS _old_ WHERE _old_.id = 3) AS _old_
WHERE _new_.id = _old_.id
RETURNING (SELECT name FROM demo WHERE id = 3) AS oldName, name AS newName;

F. Version-Specific Workarounds
For SQLite 3.35.0+, use UPDATE FROM syntax without aliases:

UPDATE demo
SET name = 'Name 2'
FROM demo AS _old_
WHERE demo.id = _old_.id AND _old_.id = 3
RETURNING _old_.name AS oldName, demo.name AS newName;

This works because _old_ is now a directly joined table, not a subquery alias.

G. Application-Side Tracking
Fetch the old value before updating:

# Python pseudocode
old_name = cursor.execute("SELECT name FROM demo WHERE id = 3").fetchone()[0]
cursor.execute("UPDATE demo SET name = 'Name 2' WHERE id = 3 RETURNING name")
new_name = cursor.fetchone()[0]
print({"oldName": old_name, "newName": new_name})

H. Schema Redesign
For audit-heavy applications, add last_updated and previous_name columns to demo:

ALTER TABLE demo ADD COLUMN previous_name TEXT;
UPDATE demo
SET previous_name = name, name = 'Name 2'
WHERE id = 3
RETURNING previous_name AS oldName, name AS newName;

I. JSON Extension
Use SQLite’s JSON functions to bundle old and new values:

UPDATE demo
SET name = 'Name 2'
WHERE id = 3
RETURNING json_object('oldName', name, 'newName', 'Name 2');

J. Composite Queries
Combine UPDATE and SELECT in a single transaction:

BEGIN TRANSACTION;
SELECT name FROM demo WHERE id = 3;  -- Store this as oldName
UPDATE demo SET name = 'Name 2' WHERE id = 3 RETURNING name AS newName;
COMMIT;

By adopting these methods, developers can circumvent SQLite’s alias scope limitations while maintaining compatibility with PostgreSQL-style workflows. Choose the approach that aligns with your transaction requirements, performance constraints, and schema flexibility.