SQL - Data modification - Part 12 - Creating Surrogate Table with correlated subquery to limit SQL delete statement
|
Haziran 7, 2012 by sql tutorial
|
|
Article Information
This SQL Tutorial contains and tries to cover following subjects:
- Explanation of SQL delete with correlated subquery in SQL
- Example to Delete a row with correlated subquery in SQL Server
- Deleting a row from JOIN result set
- Deleting a row which is result set depending on result of JOIN with another table
Article covers following key subjects:
- SQL delete row
- SQL delete from
- SQL delete statement
- SQL delete syntax
Articles tries to provide answer to following questions
- How to delete a row in the column of Table in SQL server?
- How to delete a record from database table?
- How to use sql delete with subquery and JOIN in SQL?
- What is sql delete from with JOIN subquery syntax?
Article covers followings indirectly:
- Basic query flow in SQL Server
- SQL DELETE
- SQL JOIN
- SQL subqueries
Articles pre-requisites following information:
- General knowledge of SQL Server Management Studio
- General knowledge of Basic query flow in SQL
- Basic knowledge of SQL Delete
- Basic knowledge of SQL Subqueries
SQL DELETE
In this article, we will look into SQL delete with subquery method including JOIN, which is another technique of filtering sql delete query in more advanced way. We had covered sql delete, and first advanced technique of limiting delete with SQL DELETE FROM method in earlier articles.
To recall briefly, SQL Delete command is used to delete a record - a row from a column in a sql table. SQL DELETE command works row-based. Lets remember basic syntax of sql delete before looking its advanced delete technique.
Above SQL Delete command wipes out entire column. All rows are deleted.
SQL DELETE FROM and Correlated Subquery Technique
In earlier article, we had covered first advanced technique of using JOIN in SQL DELETE statement as one FROM to term it practically. In this article, lets name it two FROM technique to be practical.
When we provide a FROM in SQL delete query, we limit the delete of rows or a row with sort of creating a surrogate - variation table that sql delete executes itself on it. Lets remember the basic forms of sql delete again to see advanced method to extend sql delete functionality.
SQL delete supports more advanced form of delete with different techniques. We had covered sql update before with using JOIN to limit update. SQL delete similar to sql update can work with JOINs in same query to create a surrogate table to execute itself on that result of JOIN. That technique is called referencing another table in sql delete query also. Referencing means, we JOIN two tables in delete query, DELETE executes itself on that result set.
In earleir article we had used following advanced technique, lets recall:
Above sql delete syntax was telling sql server to delete only rows that what JOIN result set brings to it.
We can also use a second technique as we mentioned in that sql delete query. We will use two FROM this time and a SELECT query which contains a JOIN. It works very similar to SQL DELETE FROM. Further, their execution plans are same for point of query performance.
Example to SQL delete from with Correlated Subquery and JOIN
In sql delete example, we will use same tables, but will gonna approach with another way to it. We want to delete customers which their order total amount is less than 10. Our sql delete limitation depends on orders table. We had used a technique to delete customer which has less order than 10 in orders with SQL delete from. This time we will use a correlated subquery and join another table to perform a sql delete on a JOIN result set.
Syntax for sql delete with correlated subquery is as follows:
DELETE table1 from table1
WHERE EXISTS
(SELECT * FROM table2 WHERE table1.columnX = table1.columnX AND aCondition)
Above sql delete from syntax works that way: subquery is executed and returns a result set which serves as a surrogate - a table mask. SQL delete executes itself on that surrogate table. Finally, row is deleted if exist.
Lets see it in an example:
As above sql delete from example indicates, a single row has been deleted from table. We referenced orders table in JOIN part as a subquery to filter data and bring only rows where order quantity is less than 10. That was only one customer "Zeta Corp". Finally, sql delete has been executed on that JOIN query result to omit the delete.
In next article, we will look another advanced form of sql delete which is cascaded delete.
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | |
| Loc: | articles |
| Tags: | tsql |
|