how to delete data from table A besides the customer service and director data in table B?

by 
DELETE FROM table_name
WHERE condition;

In this statement:
__

First, specify the table from which you delete data.
Second, use a condition to specify which rows to delete in the WHERE clause. If the row matches the condition, it will be deleted.
Notice that the WHERE clause is optional. If you omit the WHERE clause, the DELETE statement will delete all rows in the table.

Besides deleting data from a table, the DELETE statement returns the number of rows deleted.

To delete data from multiple tables using a single DELETE statement, you use the DELETE JOIN statement which we will cover in the next tutorial.

To delete all rows in a table without the need of knowing how many rows deleted, you should use the TRUNCATE TABLE statement to get a better performance.

For a table that has a foreign key constraint, when you delete rows from the parent table, the rows in the child table will be deleted automatically by using the ON DELETE CASCADE option.

MySQL DELETE examples
We will use the employees table in the sample database for the demonstration.

MySQL DELETE – Employees Table

Note that once you delete data, it is gone. Therefore, you should backup your database before performing the DELETE statements in the next section.
Suppose you want to delete employees whose office Number is 4, you use the DELETE statement with the WHERE clause as the following query:

DELETE FROM employees 
WHERE
officeCode = 4;

To delete all rows from the employees table, you use the DELETE statement without the WHERE clause as follows:

DELETE FROM employees;

All rows in the employees table deleted.

MySQL DELETE and LIMIT clause
If you want to limit the number of rows to be deleted, you use the LIMIT clause as follows:

DELETE FROM table
LIMIT row_count;

Note that the order of rows in a table is unspecified, therefore, when you use the LIMIT clause, you should always use the ORDER BY clause.

 DELETE FROM table_name
 ORDER BY c1, c2, ...
 LIMIT row_count;

Consider the following customers table in the sample database:

For example, the following statement sorts customers by customer’s names alphabetically and deletes the first 10 customers:

 DELETE FROM customers
 ORDER BY customerName
 LIMIT 10;

Similarly, the following DELETE statement selects customers in France, sorts them by credit limit in ascending order, and deletes the first 5 customers:
__

 DELETE FROM customers
 WHERE country = 'France'
 ORDER BY creditLimit
 LIMIT 5;

More Related Contents:

  1. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0
  2. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  3. MySQL error 1064 syntax but everything seems fine
  4. MySQL/MariaDB – order by inside subquery
  5. Cast int to varchar
  6. How can I change MariaDB to MySQL in XAMPP?
  7. How to turn on/off MySQL strict mode in localhost (xampp)?
  8. MySQL – SELECT * INTO OUTFILE LOCAL ?
  9. If two columns are 1 then make new column
  10. Return row only if value doesn’t exist
  11. Unknown column in ‘field list’ error on MySQL Update query
  12. MySQL : isn’t in GROUP BY
  13. How do I add a custom CHECK constraint on a MySQL table?
  14. SQL Query to get column values that correspond with MAX value of another column?
  15. MySQL : transaction within a stored procedure
  16. Setting column values as column names in the SQL query result
  17. Select from one table where not in another
  18. How to select the most recent set of dated records from a mysql table
  19. How to get current date & time in MySQL?
  20. Set value to NULL in MySQL
  21. Laravel join with 3 Tables
  22. SELECT query return 1 row from each group
  23. MySql Single Table, Select last 7 days and include empty rows
  24. mysql SQL: specific item to be first and then to sort the rest of the items
  25. When is a timestamp (auto) updated?
  26. MySQL Group By Hours
  27. RMySQL dbWriteTable with field.types
  28. How do you force mysql LIKE to be case sensitive? [duplicate]
  29. #1060 – Duplicate column name ‘id’
  30. In what order are MySQL JOINs evaluated?

Leave a Comment