CTE Query(common-table-expressions)

Sidhartha Patnaik

7y880SQL

Hi,

Can I use CTE for insert, update and delete opeation ?

Or its only use for select command only ???

Thanks,

Sidhartha Patnaik

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka Singh7y ago
Accepted 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.
Rajeev Kumar3y ago
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.
1. with update_cte
2. as
3. (
4. select id, name, salary from tblEmp
5. )
6. update update_cte set salary=5555 where id =2
7. select * from tblEmp;
Vinitha T4y ago
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;
Sidhartha Patnaik7y ago
Thanks Priyanka
Sidhartha Patnaik7y ago
Thanks Amit and Rajni
Rajanikant Hawaldar7y ago
Hi Sidhartha Patnaik,
Refer this, may help you
https://stevestedman.com/2012/02/cte-with-an-insert-statement/
Amit Kumar7y ago
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