Lecture Notes: Updating and Deleting Data from a Table

Introduction

• Video tutorial on updating and deleting data from a database table.
• Example uses a table of employees.

Updating Data

Updating Specific Columns

• Use the UPDATE keyword.
• Specify the table name (e.g., employees).
• Use SET to specify the column to update.
  • Example: SET hourly_pay = 10.25 for employee.
• Use WHERE clause to specify the row to update.
  • Example: WHERE employee_id = 6 to select Sheldon Plankton.
• Alternative: Can select by first name or last name.

Updating Multiple Columns

• Change more than one field at once by separating with a comma.
  • Example: SET hourly_pay = 10.50, hire_date = '2023-01-07'.
• Execute script to apply changes.

Setting a Field to Null

• To remove a value, set the field to NULL.
  • Example: SET hire_date = NULL to make hire date empty.

Updating All Rows in a Column

• Exclude WHERE clause to update all rows.
  • Example: SET hourly_pay = 10.25 affects all employees.
• Caution: This sets the column to a consistent value for all records.

Deleting Data

Deleting Specific Rows

• Use DELETE FROM followed by table name.
• Important: Always include a WHERE clause to prevent deleting all rows.
  • Example: DELETE FROM employees WHERE employee_id = 6.

Deleting All Rows

• Omitting WHERE clause will delete every record in the table.
• Warning: Do not execute without WHERE unless intended.

Conclusion

• Quick guide on updating and deleting data in database tables.
• Key takeaways: use WHERE clauses to specify updates and deletions, avoid accidental mass changes.