SQL
Using Subqueries in Update
You can use subqueries in the SET clause to update data based on values from another table:
Assume there is another table named new_ages with columns first_name, last_name, and new_age. Update the ages of employees based on the new_ages table:
UPDATE employees
SET age = (SELECT new_age FROM new_ages WHERE employees.first_name = new_ages.first_name AND employees.last_name = new_ages.last_name);
Handling NULL Values
If you want to set a column value to NULL, you can use the NULL keyword:
UPDATE employees
SET department = NULL
WHERE first_name = 'Tom' AND last_name = 'Harris';
General Tips!
- Use Proper Indexing: Index frequently queried columns to improve query performance, but avoid over-indexing as it can slow down write operations.
- Write Efficient Queries: Optimize your queries by selecting only the necessary columns, avoiding SELECT *, and using joins wisely.
- Normalize Your Data: Organize your database into normalized tables to reduce redundancy and improve data integrity.
- Use Transactions: Ensure data consistency and integrity by wrapping multiple related operations in transactions, especially in multi-step processes.
- Understand and Use SQL Functions: Leverage built-in SQL functions for data manipulation and retrieval, such as aggregate functions (SUM, COUNT) and string functions (CONCAT, SUBSTRING).