DELETE INTO SQL: Deleting and adding data

DELETE INTO in SQL is a clause that allows you to delete records from a table. At the same time, it allows you to insert new records into another table . This clause is very useful for performing data cleaning operations or for creating new tables from existing data.

When deleting records from a table, DELETE INTO ensures that only selected records are deleted, without affecting other important data. Additionally, inserting new records into a separate table allows deleted data to be preserved. For example, for audit or further analysis purposes.

With DELETE INTO, database developers can perform cleaning and optimization operations more efficiently and safely.

In this article, we will explore in detail how to use DELETE INTO in different database scenarios. We will also show you how to avoid common mistakes when working with this clause.

Syntax

The syntax of the DELETE INTO command in SQL is as follows:

DELETE INTO destination
FROM source
[WHERE condition];

The DELETE INTO command allows you to delete records from one table and insert them into another table. This eliminates the need to write a separate SELECT query and UPDATE query.

The “DELETE INTO destination” part indicates that the records must be deleted from the source table and inserted into the destination table. Therefore, it is possible that the destination is an existing table or a table created only to store the deleted records.

The “FROM source” part indicates the source table that will be deleted. This way, you can specify a specific table or a query that selects the table to delete.

The “WHERE condition” part is optional and allows you to specify a condition that limits records to being excluded. This way, when the condition is specified, it will only delete records that meet the condition.

It is important to note that the DELETE INTO command does not allow the deletion of records in tables that have established foreign keys. Thus, the deletion of records in tables with foreign keys is controlled by foreign key constraints. They prevent the deletion of records related to other records in other tables.

Differences between the DELETE INTO and the DELETE command without INTO in SQL

There is a difference between DELETE INTO statement and DELETE without INTO statement in SQL. The first allows you to insert new records into a table. The second only deletes records from the original table.

When using the DELETE INTO statement, we can specify the destination table where the deleted records are inserted. This statement can be useful when we want to create a new table with differently formatted data. Or when you need to remove old records from a table and insert new data at the same time.

On the other hand, the DELETE statement without INTO just removes records from the original table without inserting any new records into another table. Thus, this statement is useful when we need to remove specific records from the table. And we also don’t want to create a new table with the same data.

Example

Therefore, let’s consider the following example to illustrate the difference:

Suppose we have the following “customers” table with some records:

Now, suppose we need to remove John record. And also insert a new record with Pedro’s updated data in the “customers” table. In this sense, we will use the DELETE INTO statement and the DELETE without INTO statement to perform these operations:

• DELETE INTO statement:

DELETE INTO updated_clients
From clients
WHERE name = 'John';

INSERT INTO customers (name, email, telephone)
VALUES ('Pedro', 'pedro@example.com', '111111111');

In this example, we are using the DELETE INTO statement to delete John record from the “customers” table. And also insert a new record with Pedro updated data in the “updated_clients ” table.

• DELETE statement without INTO:

DELETE FROM customers
WHERE name = 'John';

In this example, we are using the DELETE statement without INTO to delete John’s record from the “customers” table without inserting any new records.

In summary, the DELETE INTO statement is useful when we need to remove old records and insert new data into a table. On the other hand, DELETE without INTO statement is useful when we need to remove specific records from the table without creating a new table.

Examples of using DELETE INTO

Let’s consider some practical examples of how to use the DELETE INTO command in SQL in different situations.

Example 1: DELETE INTO SQL duplicate records using

Suppose we have the following “customers” table:

Now, suppose we need to delete the duplicate records from the “customers” table based on the “name” field. We can use the DELETE INTO statement along with the JOIN function to identify the duplicate records and delete them from the table.

DELETE INTO customers
FROM customers
JOIN clients ON clients.name = clients_duplicates.name;

UPDATE duplicate_clients
SET id = (SELECT MAX(id) + 1 FROM customers)
WHERE name IN ('John', 'Pedro');

In this example, we are using the DELETE INTO statement to delete the duplicate records from the “customers” table based on the “name” field. We developed a JOIN query to identify duplicate records. We then use the UPDATE statement to update the value of the “id” field of each duplicate record.

Example 2: DELETE INTO SQL old records and update others

Suppose we have the following “sales” table:

Now, suppose we need to delete the old sales records of Product 1 and Product 2 and update the sales records of Product 3 and Product 4. We can use the DELETE INTO statement along with the JOIN function to identify the old records and update the records relevant sales.

DELETE INTO sales
FROM sales
JOIN sales_old ON sales.product = sales_old.product AND sales.data < sales_old.data;

UPDATE sales
SET value = 500
WHERE product IN ('Product 3', 'Product 4') AND data >= '2022-02-01';

In this example, we are using the DELETE INTO statement to delete the old sales records for Product 1 and Product 2 based on the “old_sales” table. Next, we are using the UPDATE statement to update the value of the “amount” field of each relevant sales record for Product 3 and Product 4 based on the date of sale.

Example 3: DELETE INTO SQL records based on a condition

Suppose we have the following “customers” table:

Now, suppose we need to delete all customer records with the email “ john@example.com “. We can use the DELETE INTO statement together with the WHERE function to identify records to be deleted based on the condition of the “email” field.

DELETE INTO customers
FROM customers
WHERE email = 'john@example.com';

In this example, we are using the DELETE INTO statement to delete all customer records with the email “ john@example.com “. We use the WHERE function to specify the exclusion condition based on the “email” field.

Example 4: DELETE INTO records and update others based on a condition

Suppose we have the following “sales” table:

Now, suppose we need to delete the old sales records of Product 1 and Product 2 and update the sales records of Product 3 and Product 4 based on the condition of the “date” field. So, we can use the DELETE INTO statement along with the WHERE and JOIN function to identify the old records and update the relevant sales records.

DELETE INTO sales
FROM sales
JOIN sales_old ON sales.product = sales_old.product AND sales.data < sales_old.data;

UPDATE sales
SET value = 500
WHERE product IN ('Product 3', 'Product 4') AND data >= '2022-02-01';

In this example, we are using the DELETE INTO statement to delete the old sales records for Product 1 and Product 2 based on the “old_sales” table. So, next, we are using the UPDATE statement to update the value of the “amount” field of each relevant sales record for Product 3 and Product 4 based on the date of sale. This way, we use the WHERE function to specify the update condition based on the “product” field and the date condition.

It is also possible to use other functions in conjunction with DELETE INTO, such as GROUP BY and COUNT .

Advantages and Disadvantages of DELETE INTO

DELETE INTO is an SQL clause that allows you to delete records from a table. At the same time, it allows you to insert new records into another table. It is useful in situations where you want to delete records from a table and create a new table with the same data.

Advantages of DELETE INTO:

1. Time Saving: You can delete records from a table and at the same time create a new table with the same data. This saves time compared to having to perform two separate operations.
2. Greater efficiency: DELETE INTO is more efficient than performing two separate operations. This is because the original table does not need to be loaded twice.
3. Ease of use: DELETE INTO is easy to use and can be written in just one line of code.

Disadvantages of DELETE INTO:

1. Resource limitations: DELETE INTO may be limited in terms of available system resources. This is because the operation of deleting and creating a new table can be resource intensive.
2. Data Loss: DELETE INTO deletes records from a table and then creates a new table with the same data. However, if an error occurs during operation, data loss may occur.
3. Complexity: DELETE INTO can be complex to understand and use correctly. This is because the operation of deleting and creating a new table can be complex and requires advanced knowledge of SQL.

In summary, DELETE INTO is a useful and efficient SQL clause that can save time and resources in specific situations. However, we must use it with care!

Alternatives to DELETE INTO

There are some alternatives to DELETE INTO in SQL that we can apply depending on the situation. Some of these alternatives are:

1. TRUNCATE : TRUNCATE is an SQL clause that allows you to delete all records from a table. TRUNCATE is more efficient than DELETE because it does not delete records one by one. However, TRUNCATE is not recursive. Therefore, we cannot be using it to create a new table with the same data.
2. INSERT INTO : INSERT INTO is a SQL clause that allows you to insert records into a table. INSERT INTO may be applied to create a new table with the same data as an existing table. However, this requires the existing table to be loaded twice, which may be less efficient than DELETE INTO.
3. SELECT INTO : SELECT INTO allows you to select records from a table and, at the same time, create a new table with the same data. SELECT INTO is similar to INSERT INTO, but it allows you to select specific records from a table to insert them into a new table. However, SELECT INTO may be less efficient than DELETE INTO. Because it requires the existing table to be loaded twice.
4. MERGE : MERGE is an SQL clause that allows you to combine data from two tables. MERGE can be applied to delete records from a table and, at the same time, insert new records into another table. However, MERGE may be less well-known and less common than DELETE INTO.

Security considerations when using DELETE INTO SQL

Key security considerations when using the DELETE INTO command in SQL include the possibility of accidentally deleting important records . This is because DELETE INTO allows you to delete records from one table and, at the same time, insert new records into another table. Thus, leading to accidental deletion of important records if we are not careful while using the clause.

Another important security consideration is verifying data before deleting records. It is important to check that the records that will be deleted are actually the correct ones and that they will not be needed in the future . In this sense, deleting important records by mistake can lead to significant problems in a system.

It is also important to have a data backup before using DELETE INTO . Therefore, if an error occurs during operation, data may be lost. Having a data backup allows data to be restored if necessary.

In summary, the main security considerations when using the DELETE INTO command in SQL include checking data before deleting records. Also verifying data before inserting records and having a data backup to restore if necessary. Therefore, these measures help minimize the risk of accidental deletion of important records.