How to UPDATE and DELETE data from a TABLE

Back to: Structured Query Language (SQL)

---

We are continuing with our myDB database and the employees table created in a previous lesson.

Here we will UPDATE and DELETE data from a TABLE.

UPDATE employees
SET hourly_pay = 10.25
WHERE employee_id = 6;
SELECT * FROM employees;

Running this this in MySQL Workbench may throw an error:

To fix this in MySQL Workbench: Edit –> Preferences –> SQL Editor –> uncheck “Safe updates”:

Click OK –> you will need to reconnect to the database by closing the Query1 tab.

You can change more than one field at once, after the first change, add a comma, then make a second change:

UPDATE employees
SET hourly_pay = 10.50,
    hire_date = "2023-03-10"
WHERE employee_id = 6;
SELECT * FROM employees;

To set a field to NULL meaning “no value”, we simply SET <field_name> = NULL – we can remove Plankton’s hire_date by:

UPDATE employees
SET hourly_pay = 10.50,
    hire_date = NULL
WHERE employee_id = 6;
SELECT * FROM employees;

We can set ALL of the values in a column to a specific value. If we want to make everyone’s hourly pay the same:

UPDATE employees
SET hourly_pay = 16.50;
SELECT * FROM employees;

To delete a row from a table, we use DELETE FROM employees, (which alone would delete the entire contents of the table, so ensure you a WHERE clause – to delete Employee 6:

DELETE FROM employees
WHERE employee_id = 6;
SELECT * FROM employees;

---