SQL - Data modification - Part 11 - Creating Surrogate Table 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 in SQL
- SQL delete a row
- SQL delete statement
- SQL delete syntax
- SQL delete FROM
- Example to Delete a row from Table in SQL
- Deleting a row from JOIN result set
- Deleting a row which is result set depending on result of JOIN with another table
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 JOIN in SQL?
- What is sql delete from syntax?
Article covers followings indirectly:
- Basic query flow in SQL Server
- SQL DELETE
- SQL JOIN
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
SQL DELETE
In this article, we will look into SQL delete with JOIN, the more advanced technique of sql delete in sql server. We had covered sql delete and its basic form and sql delete syntax.
To recall briefly, SQL Delete command is used to delete a record - a row from a column in a sql table. 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 and JOIN in same query
In earlier article, we had covered most cases use of sql delete; that was WHERE condition to limit how many rows are going to be deleted. SQL Delete with WHERE condition syntax was as follows:
DELETE table1 where columnName1 = value1
Above sql delete syntax was telling sql server to delete only rows that where clause match.
Apart from that basic use of sql delete, sql delete also 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. It can lead confusion to discuss its theory, therefore lets first pick up a real life scenario and use it in example to understand easier.
Assume that we have a customers table with few records that contain some customer name and a status column that indicates the customer whether its active or passive. To know that customer is active or passive, we join orders table to it, to look if that customer has any order or not. In earlier sql update article, we had updated status column in sql update example with using JOIN. SQL delete works similar way, but this time it deletes instead update.
In normal basic form of sql delete, sql delete works on one table. SQL delete can delete one row, all rows or amount of rows depend on a WHERE clause. However, in our scenario, we need to delete a customer record but we also want to delete that customers that if they do not have orders amount of particular number. For instance, we want to clean our customers table and want to remove customer from table if total amount of order is less than 10. For scenario, 100 total is our threshold point to clean our customers table. Nature of sql delete statement is a delete on a given table. However, we need to tell sql delete to delete depending on a result of join with another table. In that case, we need to reference that table, way creating a surrogate - a variation table that sql delete executes itself on that table. It is some sort of masking a table.
To understand it easier, lets look at it in a sql delete from example.
Example to SQL delete with referencing another table
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 will look orders table to quantity column to find total of order of each customer.
Lets remember the basic sql delete syntax with WHERE clause:
DELETE table1 where columnName = value1
Above sql delete deletes the row where condition is ok. For instance:
DELETE table1 where customerName = 'Beta Corporation'
Above sql delete command will delete only the row where indicated column name and its value match. Now we need to add a reference to another table to extend sql delete statement. Syntax will be like as follows:
DELETE table1 FROM table1
JOIN table2
ON table1.column1 = table2.column2 WHERE clause
Lets see it in an example:
As above sql delete example indicates, a single row has been deleted from table. We referenced orders table in JOIN part 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 delete.
In next article, we will look another sql delete technique of referencing another table.
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | |
| Loc: | articles |
| Tags: | tsql |
|