T-SQL query: CTE, T-SQL: CTE

T-SQL query: CTE, T-SQL: CTE

I have seen a simple example of the with as Clause in two books. I have not found any relevant information on the Internet.

Summary:

[Batch update of large tables]

[Batch Delete large tables]

[Keep only one row for completely repeated rows]

-- Create a test TABLE -- drop table [tabName] SELECT * INTO [tabName] FROM sys. objectsSELECT * FROM [databaseName]. [dbo]. [tabName] order by name desc

Batch [batch update of large tables] When the webpage needs to be upgraded, the database needs to add or update the field value, which will be blocked for a long time. Generally, you can add a field to allow null values, update the default values in the table, and add constraints. For example, to update the principal_id of the test table to 0, use the simplest and feasible method to update the table :; with tab as (select top 10 principal_id FROM [dbo]. [tabName] where principal_id is null) update TAB set principal_id = 0 to update the field principal_id, take only one. Each time you select the first 10 rows for update, the value of null is 0. You can create a scheduled job update. Batch [batch deletion of large tables] a large number of data needs to be deleted for some data maintenance, and the table is large and many users are still using it. Generally, you can create a job to delete it at night or delete it in segments based on a field. You can also delete the first N rows based on the selected conditions. with tab as (select top 10 principal_id FROM [dbo]. [tabName] where principal_id is null) delete from tab distinct [retain only one row for completely repeated rows] -- insert into [tabName] select top 50 PERCENT * FROM [databaseName]. [dbo]. [tabName] SELECT * FROM [databaseName]. [dbo]. [tabName] ORD In the cases where er by name desc is the most widely used on the Internet, it must be unique to specify a column to use a statement to delete other duplicates. All identical rows indicate creating a temporary table for the transition operation. The following uses the "with clause and ROW_NUMBER () function" to delete completely duplicated rows. When grouping partition, you can SELECT a column for group sorting. with tab as (SELECT ROW_NUMBER () over (partition by object_id order by (select 0) idFROM [dbo]. [tabName]) delete from tab where id> 1 partition ------------------------------------------------------------------------------------------------------------------------------