SQL SET

What does the SQL SET keyword do?

Modifies column values in an UPDATE statement or changes session-level variables and options.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SET:

SQL SET Full Explanation

SET serves two core purposes in SQL:1. UPDATE clause – In every major dialect, SET appears inside an UPDATE statement to assign new values to one or more columns in the rows that satisfy the WHERE filter. Each column is paired with an expression, literal, sub-query, or function call. If the WHERE clause is omitted, the assignment affects every row in the table.2. Stand-alone statement – Many databases (PostgreSQL, SQL Server, MySQL, Oracle, Snowflake) implement a separate SET command that alters run-time settings (e.g., search_path, ANSI_WARNINGS) or assigns values to local/session variables. The exact syntax, available options, and scoping rules vary by vendor, but the intent is the same: change state for the current transaction, session, or connection.Key behaviors and caveats:- ORDER of assignments does not influence execution; all expressions are evaluated on the original row state unless the dialect specifies otherwise.- Expressions may refer to columns being updated (self-assignment) but not to aliases defined in the same SET list.- The stand-alone form is not part of the ANSI standard; always confirm syntax in your dialect documentation.- Permissions: UPDATE requires UPDATE privileges on the target table; session SET requires proper role or ALTER SESSION permissions.- In transactional databases, SET inside UPDATE is rolled back if the transaction is rolled back. Stand-alone SET options may or may not roll back, depending on the database (for example, SET LOCAL in PostgreSQL reverts at transaction end).

SQL SET Syntax

-- Clause inside UPDATE
UPDATE table_name
SET column1 = expression1,
    column2 = expression2
[WHERE condition];

-- Stand-alone session/variable commands (dialect-specific)
-- PostgreSQL
SET search_path TO analytics, public;
SET LOCAL statement_timeout = '2s';

-- MySQL
SET @rows := 0;
UPDATE orders SET total = total * 1.05 WHERE status = 'OPEN';

-- SQL Server
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SQL SET Parameters

table_name (identifier) - Target table to update.
column (identifier) - Column to assign.
expression (any) - Value or calculation assigned to the column.
condition (boolean) - Optional filter limiting affected rows.
option_name (identifier) - Session or system option (stand-alone SET).
variable / @variable (identifier) - User or local variable (dialect-specific).

Example Queries Using SQL SET

-- 1. Give every active subscriber a 10% credit
UPDATE subscriptions
SET credit_cents = credit_cents + (credit_cents * 0.10)
WHERE status = 'ACTIVE';

-- 2. Toggle a feature flag just for this session (PostgreSQL)
SET enable_hashagg TO off;
SELECT run_long_report();
RESET enable_hashagg;

-- 3. Capture affected rows in MySQL
SET @updated := 0;
UPDATE customers SET tier = 'gold', @updated := ROW_COUNT()
WHERE lifetime_spend > 10000;
SELECT CONCAT('Rows upgraded: ', @updated) AS msg;

Expected Output Using SQL SET

UPDATE returns the number of rows modified. Query planners update the specified columns; other columns remain unchanged.
Session option changes take effect immediately for the current connection; RESET restores the previous value.
Variable assignment stores the number of affected rows, then SELECT returns a confirmation message.

Use Cases with SQL SET

Correct data errors by overwriting wrong values.
Mass-migrate schema changes (populate a new column from old data).
Grant promotional credits or price increases in bulk.
Turn on or off optimizer hints or diagnostics for troubleshooting.
Store intermediate results in session variables for multi-step scripts.

Common Mistakes with SQL SET

Omitting the WHERE clause and unintentionally updating every row.
Using single-row sub-queries that return multiple rows, causing runtime errors.
Expecting stand-alone SET to persist after the session closes.
Mixing UPDATE SET with JOIN syntax incorrectly (dialect differences between MySQL and SQL Server).
Assuming variable assignment syntax (@var :=) works identically across databases.

First Introduced In

SQL-92 (UPDATE ... SET). Vendor-specific stand-alone SET appeared earlier (e.g., Sybase SQL Server 4.x).

Frequently Asked Questions

Does SET always require a WHERE clause?

No. Without WHERE, every row in the table is updated. Add a WHERE clause to target specific rows and avoid accidental data loss.

Is the stand-alone SET statement ANSI standard?

No. Only the UPDATE ... SET clause is standardized. Stand-alone SET for session options and variables is implemented differently by each vendor.

Can I update multiple columns with one SET clause?

Yes. Separate each column-expression pair with commas inside the SET list: SET col1 = val1, col2 = val2.

Does SET roll back on transaction failure?

UPDATE ... SET changes roll back if the transaction fails. Session-level SET may or may not roll back, depending on your database. Use PostgreSQL's SET LOCAL for transactional scope.