SQL Row Deletion Explained [10 Practical Examples] | GoLinuxCloud (2023)

Overview of SQL delete rows

A delete request is expressed in the same way as a query. We can only remove whole tuples, so one row, we cannot remove values ​​only in specific attributes.

SQL Delete Row is used to remove existing records from a table. We can delete a single record or multiple records depending on the condition we specify in the WHERE clause. If you omit the WHERE clause in a DELETE statement, SQL removes all rows from the table

SQL row deletion syntax

FROM remove table source [,...n] | see_name | table name | <object> [ WHERE { <search condition> | predicates } ][SORT BY...][LIMIT count_row]

Here,

• VON:An optional keyword that can be used between the DELETE keyword and the targettable_or_view_name, Örowset_limited_function.
• Table alias:The alias specified in the FROMsource_table- Clause representing the table or view from which rows will be removed.
• Table source or view name:The name of the table or view from which rows will be removed.
• WO:Specifies the conditions used to limit the number of rows removed. If no WHERE clause is specified, DELETE removes all rows from the table.
• SORT BY:If you specify the ORDER BY clause, rows are removed in the order specified.
• BORDER:The LIMIT clause is used to limit the number of rows that can be deleted. These clauses apply to single-table dropping, but not to multiple-table dropping.

SQL Delete first finds all tuples in table_source for which the predicates are true and then removes them from the source table

Examples of SQL delete rows

Consider a hospital database with five tables for SQL drop examples
patient table

doctor's table

content plan

laboratory table

coffee table

Example-1: SQL deletes all rows

By bypassing the where clause with the SQL query to delete rows, all rows can be deleted from the table source

In this example, we delete all room details from the rooms table using the Delete row SQL query

DELETE FROM THE ROOM

• When we run the above query, it will show messages like 6 rows deleted or 6 rows affected because there are 6 records in the source space of the table
• The table in the space has no relation to any other table in the same database, so the system allows the deletion, if it doesn't, the error message "Execution failed, the delete statement conflicts with the foreign key referrer of the constraint" in the SQL -Server

PRODUCTION:

To see the SQL delete row result, we need to use the SQL select statement, the result shows an empty table structure because all the rows have been deleted with the above query

SELECCIONE room_no, room_type, statusFROM room

Example-2: SQL delete line with WHERE condition

How to delete multiple rows or a set of sql rows where sql clause apply row delete query to specify the condition

Use SQL Delete multiple Rows to remove all data from the billing table for patients who paid room rates over 800

DELETE FROM billWHERE (Zimmergebühr > 800)

• In the above SQL query, the where clause is used to specify the condition to remove the data of the patient who paid room rates for more than 800
• When we run the above query, it shows a result message like 2 rows affected or deleted because there are 2 records that meet this condition

PRODUCTION:

After running the above SQL row query, the two highlighted rows will be removed from the table list

Example-3: DELETE statement with more than one condition

With SQL Delete Row, a SQL where clause with relational operator is used with the SQL query Delete Row to delete rows based on two or more conditions

Remove account records from the table if the patient has been hospitalized more than 5 days and has paid more than 900 room charges

DELETE FROM bill WHERE (#_of_days > 5) AND (room_charge > 900)

• In the SQL row query above, two conditions were specified with the where clause connected with the AND relationship operator. So if both conditions are layered to a specific record, that row will be deleted after executing the above query.
• Executing the above query shows the result as no rows are affected as there are no records in the invoices table that meet the above conditions.

PRODUCTION:

Select * from the invoice

Example-4: SQL delete row with TOP with WHERE clause

The SQL line DELETE with top statement is used to delete records from a table and limit the number of records deleted to a fixed amount or percentage.

SQL DELETE line with top clause syntax

Remove top(top_value) [percent] because tabela source [where condition];

In this example, we delete a maximum of 5 records from the lab table where the invoice value is greater than 800

DELETE ABOVE (5) FROM WHERE Lab (amount > 800)

• When the above SQL query is executed to delete rows, two records are deleted from the table lab_no 50 and lab_no 60 which have a quantity value of 900
• If more than 5 records meet the quantity value condition greater than 800, only the first 5 records will be deleted, the rest will not be deleted from the table.

PRODUCTION:

Example-5: SQL delete row with ORDER BY clause

The ORDER BY and LIMIT keywords can be used with SQL Delete Row to delete only a defined number of rows, with the columns sorted in a specific order. The ORDER BY clause arranges the columns in a specific order, and the LIMIT keyword removes only the number of rows specified by the numeric value immediately followed by the LIMIT keyword.

Delete the two records with the highest paid invoice amounts from the Invoice table using SQL Delete Row

DELETE FROM bill ORDER BY billmount DESC LIMIT 2;

• When the above SQL row query is run first, it sorts the invoice table rows in descending order by the "Invoice Amount" column.
• Then just delete two (2) lines from the top

Example-6: SQL delete row from SELECT subquery

The SQL query to delete rows can also delete records based on the result of the subquery used as the conditional parameter to which the condition applies.

In this example, we remove from the billing table the record that has a minimum account amount compared to all other patient account amounts.

DELETE FROM billWHERE (billmount =(SELECT MIN(billmount) AS Expr1 FROM bill AS bill_1))

• When we execute the above query, the subquery of the SQL select statement is executed first, and the resultant value of the query, which is the value of the minimum amount of the invoice amount, is used as the condition value for querying the external SQL delete row.
• The query above removes a row with a minimum quantity value

PRODUCTION:

Select * from the invoice

Example-7: SQL delete line with EXISTS with where clause

• ·You may want to delete records in one table based on values ​​in another table. The exist clause is used with the where clause to connect to the subquery
• The SQL EXISTS condition is used in combination with a subquery and is true if the subquery returns at least one row.

Use SQL delete row query to deleteforlaboratory table medical records

DELETE FROM LAB WHERE EXISTS (SELECT doctor_id, name, age, gender, address FROM doctorWHERE (doctor_id = lab.doctor_id) AND (name = 'vini'))

• In the SQL query above to delete rows, the inner subquery is executed first and returns records where the doctor's name is vini and the record containing the value is in the lab lookup table
• Then, if the same record exists in the lab table, that record is deleted using the Delete Row SQL query

Example-8: SQL Delete Row to remove duplicate rows

SQL removes duplicate rows with GROUP BY and HAVING clause

In this method, we use the SQLGROUP BY clause to identify duplicate rows. The Group By clause groups the data by the defined columns and we can use thoseCOUNT functionto check the appearance of a line.

Use Delete Row SQL query to remove duplicate rows from lab table

How to find duplicate records

SELECT Patient_id, doctor_id, data, valor, COUNT(*) AS CNTFROMatorial GROUP BY Patient_id, doctor_id, data, valor HAVING (COUNT(*) > 1)

• The above SQL select statement query finds and lists duplicate records from the lab table
• To remove these duplicate records, we can put this query as a subquery in the where clause of the SQL Delete row
• We need to keep a single row and remove duplicate rows. We just need to remove the duplicate rows from the table

SQL query delete row to remove duplicate records

DELETE FROM lab WHERE (lab_no NOT IN(SELECT MAX(lab_no) AS Maxlabno FROM lab AS lab_1GROUP BY Patient_id, doctor_id, data, quantidade))

• When the above query is run, the single duplicate record is removed from the lab table.
• In the query above, we used the SQL MAX function to calculate the maximum ID for each row of data.

PRODUCTION:

Example-9: SQL delete line with line number

In SQL, ROW_NUMBER is a function that adds a unique incrementing number to the resulting recordset. The order in which line numbers are applied is determined by the ORDER BY expression,

The ROW_NUMBER function can be used with SQL Delete Row to delete rows based on row numbers

SELECCIONE bill_no,patient_id, doctor_charge,Row_number () SOBRE (PARTICIÓN POR bill_no ORDER BY bill_no) AS ROW_NBR FROM bill

PRODUCTION:

To delete the first four rows based on invoice number order using SQL Delete Row

DELETE FROM fatura onde (invoice number, doctor's fee) INPUT (select invoice number, doctor's fee from (SELECT invoice number, patient_ID, doctor's fee, line number () OVER (PARTITION BY invoice number ORDER BY invoice number) AS INVOICE RECORD_NUMBER ) where RECORD_NBR < 4 )

• In the above SQL row query, the ROW_Number function is used in the innermost nested select query to generate row numbers based on an order of bill_no
• Then the outer select statement gets the numeric data of the first 4 rows of the result set, the outer delete row SQL query deletes these 4 rows from the calculation table

Example-10: Delete SQL row with foreign key

When a row in a table is deleted, the same is deleted in xref tables referencing the primary key in that table. ON DELETE CASCADE is added when a table is created with the create table statement

Example 10 - Create a table with a foreign key that has a cascade delete function to delete records from both the parent table and the reference table

CREATE TABLE newbill(no_of_days int, FOREIGN KEY(paciente_id) REFERENCIAS paciente(paciente_id) ON DELETE CASCADE);

• After the above box table statement is executed, if the records have been deleted from the parent table, the existing records in the foreign key table will be automatically deleted
• So if a patient record is deleted or removed from the patients table, the same record is deleted from the foreign key table.

Summary

In this SQL delete rows article, we have covered the full explanation of SQL delete rows query with practical examples. First explained how to use delete row command with syntax, then several examples like delete all rows, delete multiple rows, delete based on where condition. , SQL delete row with exist clause and delete duplicate rows from a table with examples

references

SQL GROUP BY statement
SQL CON clause

read another

DELETE SQL statement