UPDATE with Subquery
UPDATE with Subquery is a SQL statement in the DML Statements category. Updates a column using a value computed from a subquery. Can be correlated or independent. The syntax is UPDATE table SET col = (SELECT agg FROM other_table WHERE ...) WHERE ...;. It returns rows updated. A typical example: UPDATE employees e SET dept_budget = ( SELECT budget FROM departments d WHERE d.id = e.dept_id ); -- Each employee gets their department budget -- Conditional update: UPDATE products p SET discount_price = ( CASE WHEN (SELECT COUNT(*) FROM inventory WHERE product_id = p.id) > 100 THEN p.price * 0.8 ELSE p.price * 0.95 END ); A close relative is INSERT INTO, which inserts new rows into a table. A close relative is UPDATE, which modifies existing rows in a table. Always use WHERE to avoid updating all rows. A close relative is DELETE, which removes rows from a table. Always use WHERE to avoid deleting all rows.