If you attempt to insert a record that violates a UNIQUE constraint, the database will reject the operation and generate an error. This constraint ensures that all values in the specified column must be distinct. For example, if the Email column in a Users table has a UNIQUE constraint and a new user is added with an email address that already exists in the table, the insertion will fail. The database maintains this rule to prevent duplication of key information that should remain unique, such as usernames, national insurance numbers, or email addresses. The error will typically halt the execution of the SQL script at that point unless the command is part of a larger transaction that can be rolled back. To prevent this, you can use INSERT IGNORE or check for existing values first with a SELECT query. Understanding constraints like UNIQUE is essential for maintaining data accuracy and enforcing business rules at the database level.

Yes, subqueries can be used within UPDATE and DELETE statements to dynamically identify which records to modify or remove based on conditions from other tables. This is particularly useful in relational databases where data is spread across multiple tables. For example, in an UPDATE, you might want to change the Status field in a Customers table based on whether that customer has made any recent purchases. You could use a subquery in the WHERE clause to check for their presence in an Orders table. Similarly, in a DELETE operation, a subquery can be used to delete all users who haven’t logged in within the last year by comparing their last login date from another table. The ability to embed subqueries allows for more powerful and context-sensitive data manipulation. However, subqueries must be written carefully to avoid performance issues, and they must return the correct number of values expected by the outer query.

The DELETE statement is used to remove specific rows from a table based on a WHERE condition. It logs each row deletion and can be rolled back if enclosed in a transaction. TRUNCATE, on the other hand, removes all rows from a table instantly and permanently without logging individual row deletions. It is faster than DELETE for clearing large tables but cannot be rolled back in many database systems. TRUNCATE also resets any auto-increment counters associated with the table, whereas DELETE does not. Additionally, TRUNCATE bypasses triggers in most systems, meaning any logic attached to row deletions (e.g. logging or cascading deletions) will not be executed. This difference is critical: if you need to remove specific records or ensure triggers run, use DELETE; if you want to quickly empty a table without needing to log or reverse it, use TRUNCATE. Choosing the wrong one can lead to significant data loss or logic errors.

You can update data in one table using values from another table by combining the UPDATE statement with a JOIN. This allows data from a related table to be referenced when determining new values. For example, suppose you have a Products table and a Discounts table. You can update the price of each product by applying a discount stored in the second table. This is done by joining the two tables on a common field, such as ProductID. The syntax may vary slightly between SQL dialects, but the general structure is: UPDATE Products SET Price = Price * Discounts.Rate FROM Products JOIN Discounts ON Products.ProductID = Discounts.ProductID. This technique is particularly powerful for keeping related tables synchronised or applying bulk changes based on lookup tables. However, it’s important to ensure that the JOIN returns the correct matches to avoid unintended updates. Always test such queries with a SELECT version before executing the UPDATE.

Before running a DELETE command in a production environment, several precautions should be taken to ensure data is not lost unintentionally. First, always back up the affected data. This could mean creating a full database backup or duplicating the specific table or rows involved. Next, thoroughly test the WHERE clause using a SELECT statement to preview which records will be deleted. This helps confirm that your logic targets the intended rows. You should also ensure that referential integrity constraints, such as foreign keys, won’t be violated or that you understand what cascading deletions might occur. In many cases, you should perform the operation inside a transaction so it can be rolled back if something goes wrong. Additionally, inform relevant stakeholders and ideally run deletions during low-traffic periods to minimise disruption. Logging the deletion action, including time, user, and criteria used, is also advisable for audit and recovery purposes. Careful planning avoids catastrophic data loss and maintains system reliability.