CTE Query(common-table-expressions)

Question

Hi, Can I use CTE for insert, update and delete opeation ? Or its only use for select command only ??? Thanks, Sidhartha Patnaik

Priyanka Singh · Answer

Using the CTE – We can define CTEs by adding a WITH clause directly before SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include one or more CTEs seperated by commas. The following syntax can be followed: [ WITH [, ...]] ::= cte_name [(column_name [, ...])] AS (cte_query) After you define your WITH clause with the CTEs, you can then reference the CTEs as you would refer any other table. However, you can refer a CTE only within the execution scope of the statement that immediately follows the WITH clause. After you’ve run your statement, the CTE result set is not available to other statements.

Amit Kumar · Answer

Hi, definitely You can perform these operations SELECT, INSERT, UPDATE, DELETE usin CTEFor more detailshttps://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017

Rajanikant Hawaldar · Answer

Hi Sidhartha Patnaik, Refer this, may help you https://stevestedman.com/2012/02/cte-with-an-insert-statement/

Sidhartha Patnaik · Answer

Thanks Priyanka

Sidhartha Patnaik · Answer

Thanks Amit and Rajni

Vinitha T · Answer

CTE is referenced by SELECT, INSERT, UPDATE and DELETE operations that immediately follows the CTE expression. CTE is also used along with VIEWS, MERGE and TRIGGER. Update operation with CTE: WITH CTE_Table AS( SELECT * FROM dbo.Table WHERE Column1 IS NULL ) UPDATE CTE_Table SET Column1 = '' Delete operation with CTE: WITH CTE_Table AS( SELECT * FROM dbo.Table WHERE Column1 IS NULL ) DELETE FROM CTE_Table;

Rajeev Kumar · Answer

A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement that references some or all the CTE columns . A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view. with update_cte as ( select id, name, salary from tblEmp ) update update_cte set salary=5555 where id =2 select * from tblEmp;