Solved

DML on CTE?

Posted on 2013-10-24
4
468 Views
Last Modified: 2013-11-04
what DMLs can do inside a CTE resultset?
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:MohitPandit
ID: 39596811
Hello,

You can have
SELECT
statement inside of CTE parenthesis but after close CTE parenthesis then you can have
INSERT, DELETE, UPDATE, SELECT

For example,
USE AdventureWorks2012;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0; 

Open in new window


Kindly take a look over it and let me know in case you have any concern.

Best Regards,
Mohit Pandit
0
 
LVL 5

Author Comment

by:25112
ID: 39596944
thanks Mohit.. in your example, can you do INSERT, DELETE, UPDATE on Parts CTE?
0
 
LVL 5

Accepted Solution

by:
MohitPandit earned 500 total points
ID: 39597000
Hello,

Please find below example with temp table data:

CREATE TABLE #T1 (Pid Int IDENTITY PRIMARY KEY, Name VarChar(50), Address VarChar(50))

INSERT INTO #T1(Name)
VALUES ('Ram'), ('Sham'), ('Sudhir')

-- Just fetch
WITH parts (id, name)
AS
(
	SELECT Pid, Name
	FROM #T1
)
SELECT * FROM parts;

-- Insert using CTE
WITH parts (id, name)
AS
(
	SELECT Pid, Name
	FROM #T1
)
INSERT INTO parts (Name)
VALUES ('Henri');

-- Update using CTE
WITH parts (id, name)
AS
(
	SELECT Pid, Name
	FROM #T1
)
UPDATE parts
SET name = 'Ramlal'
WHERE id = 1;

-- Delete using CTE
WITH parts (id, name)
AS
(
	SELECT Pid, Name
	FROM #T1
)
DELETE parts
WHERE id = 2

SELECT * FROM #T1;

-- Can create different table as well, here temp table
	-- on this #T2 temp table, you can do further calculation if want.
WITH parts (id, name)
AS
(
	SELECT Pid, Name
	FROM #T1
)
SELECT * INTO #T2 FROM parts

SELECT '#T2 Table', * FROM #T2

DROP TABLE #T1
DROP TABLE #T2

Open in new window


Kindly take a look over it and let me know in case you have any concern.

Best Regards
Mohit Pandit
0
 
LVL 5

Author Comment

by:25112
ID: 39622418
thank you Mohit
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question