Scribbles for my bad memory

1. Multiple Column Updates using IN clause:

UPDATE table_name
SET (col1, col2, col3) = 
    (SELECT val1, val2, val3 FROM source_table WHERE condition)
WHERE condition;

2. MERGE statement for conditional updates/inserts:

MERGE INTO target_table t
USING source_table s
ON (t.key = s.key)
WHEN MATCHED THEN
    UPDATE SET t.col1 = s.col1, t.col2 = s.col2
WHEN NOT MATCHED THEN
    INSERT (col1, col2) VALUES (s.col1, s.col2);

3. Using DEFAULT values in updates:

UPDATE employees
SET (salary, commission) = (DEFAULT, DEFAULT)
WHERE department_id = 90;

4. Updating with RETURNING clause to get modified values:

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20
RETURNING employee_id, salary INTO :emp_id, :new_salary;

5. Using row constructor expression:

UPDATE table_name
SET (col1, col2) = (SELECT * FROM (VALUES (val1, val2)));

6. Conditional updates using CASE:

UPDATE employees
SET salary = CASE 
    WHEN department_id = 10 THEN salary * 1.1
    WHEN department_id = 20 THEN salary * 1.2
    ELSE salary
END;

7. Using WITH clause (Common Table Expression) in updates:

WITH avg_sal AS (
    SELECT dept_id, AVG(salary) avg_salary
    FROM employees
    GROUP BY dept_id
)
UPDATE employees e
SET salary = (
    SELECT a.avg_salary 
    FROM avg_sal a 
    WHERE e.dept_id = a.dept_id
)
WHERE condition;

These syntaxes are particularly useful when:

• You need to update multiple columns at once
• The update values come from another table or subquery
• You want to perform conditional updates
• You need to track what was updated
• You're dealing with complex data transformations

Remember that while these features provide more elegant solutions, it's important to:

1. Test performance with your specific data volumes
2. Ensure the business logic is clear to other developers
3. Consider maintainability of the code
4. Check if indexes are being used effectively