6.1 Inserting, updating, and deleting data

SQL's Data Manipulation Language (DML) lets you modify database contents. You'll learn how to add new records with INSERT, change existing data using UPDATE, and remove unwanted information through DELETE statements.

These operations form the backbone of data management in SQL. By mastering INSERT, UPDATE, and DELETE, you'll be able to keep your database current and accurate, reflecting real-world changes in your data.

Inserting Data

INSERT Statement Syntax and Usage

Top images from around the web for INSERT Statement Syntax and Usage

• 

  php - How to insert values into MySQL database from a dynamic html table? - Stack Overflow View original

  Is this image relevant?

• 

  SQL Insert Statement with Example View original

  Is this image relevant?

• 

  Add More rows into the existing table with PHP & JQuery View original

  Is this image relevant?

• 

  php - How to insert values into MySQL database from a dynamic html table? - Stack Overflow View original

  Is this image relevant?

• 

  SQL Insert Statement with Example View original

  Is this image relevant?

1 of 3

Top images from around the web for INSERT Statement Syntax and Usage

• 

  php - How to insert values into MySQL database from a dynamic html table? - Stack Overflow View original

  Is this image relevant?

• 

  SQL Insert Statement with Example View original

  Is this image relevant?

• 

  Add More rows into the existing table with PHP & JQuery View original

  Is this image relevant?

• 

  php - How to insert values into MySQL database from a dynamic html table? - Stack Overflow View original

  Is this image relevant?

• 

  SQL Insert Statement with Example View original

  Is this image relevant?

1 of 3

• INSERT statement adds new rows to a table
• Syntax:

  INSERT INTO table_name (column1, column2, ...) [VALUES](https://www.fiveableKeyTerm:values) (value1, value2, ...)

• Specify the table name and the columns to insert data into
• If all columns are being inserted, column names can be omitted
• Example:

  INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'John', 'Doe')

VALUES Clause and Multiple Row Insertion

• VALUES clause specifies the values to be inserted into the corresponding columns
• Multiple rows can be inserted in a single INSERT statement
• Separate each set of values with commas
• Example:

  INSERT INTO products (product_id, name, price) VALUES (1, 'Phone', 500), (2, 'Laptop', 1000)

Subqueries in INSERT Statements

• Subqueries can be used in INSERT statements to insert data based on the result of a SELECT statement
• Subquery must return the same number of columns as specified in the INSERT statement
• Useful for inserting data from one table into another
• Example:

  INSERT INTO new_customers (customer_id, name) SELECT customer_id, name FROM customers [WHERE](https://www.fiveableKeyTerm:where) country = 'USA'

Updating Data

UPDATE Statement Syntax and Usage

• UPDATE statement modifies existing rows in a table
• Syntax:

  UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition

• SET clause specifies the columns to be updated and their new values
• WHERE clause filters the rows to be updated based on a condition
• Example:

  UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales'

Subqueries in UPDATE Statements

• Subqueries can be used in UPDATE statements to update data based on the result of a SELECT statement
• Subquery can be used in the SET clause or the WHERE clause
• Useful for updating data based on values from another table
• Example:

  UPDATE orders SET status = 'Shipped' WHERE order_id IN (SELECT order_id FROM order_details WHERE quantity > 10)

Deleting Data

DELETE Statement Syntax and Usage

• DELETE statement removes rows from a table
• Syntax:

  DELETE FROM table_name WHERE condition

• WHERE clause filters the rows to be deleted based on a condition
• If WHERE clause is omitted, all rows in the table will be deleted
• Example:

  DELETE FROM customers WHERE customer_id = 10

Subqueries in DELETE Statements

• Subqueries can be used in DELETE statements to delete data based on the result of a SELECT statement
• Subquery is typically used in the WHERE clause
• Useful for deleting data based on values from another table
• Example:

  DELETE FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA')

Advanced DML

MERGE Statement

• MERGE statement performs insert, update, or delete operations based on a condition
• Useful for synchronizing data between two tables
• Syntax:

  MERGE INTO target_table USING source_table ON (condition) WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT VALUES ...

• Example:

  MERGE INTO customers c USING new_customers nc ON (c.customer_id = nc.customer_id) WHEN MATCHED THEN UPDATE SET c.email = nc.email WHEN NOT MATCHED THEN INSERT VALUES (nc.customer_id, nc.name, nc.email)

Complex Subqueries in DML

• Subqueries can be used in various parts of DML statements (INSERT, UPDATE, DELETE, MERGE)
• Subqueries can be nested to perform complex data manipulations
• Correlated subqueries can reference columns from the outer query
• Example:

  UPDATE employees e SET e.salary = (SELECT AVG(salary) FROM employees WHERE department = e.department) WHERE e.employee_id IN (SELECT employee_id FROM performance_ratings WHERE rating = 'Excellent')