asked on

How do I use an INTERSECT SELECT to delete matching records from one of the tables used in the select?

How do I use an INTERSECT SELECT to delete matching records from one of the tables used in the select?

The goal is to DELETE FROM the table that does not contain matches (NoMatch table) that have now been found in the Match table. HOWEVER, the requirement is to delete "the same number of records found in the match table." For example, if my "NoMatch" table contains 3 matching records, and the Match table has found 2 matching records, delete only 2 records from the NoMatch table.

I have example code that will make it easy to see. Joins have not proven to work. I'm open to see a response that can demonstrate an action query (delete update insert) using the example provided. Thank you!

--use frs
--------------------------------------------CREATE---------------------------------------------------------------------------------------
CREATE TABLE xWorkTable	(ID INT IDENTITY (1,1) NOT NULL, W_vMatchRecord  VARCHAR (20), vSystem VARCHAR (3), PRIMARY KEY (ID));
CREATE TABLE xNoMatch	(ID INT IDENTITY (1,1) NOT NULL, NM_vMatchRecord VARCHAR (20), vSystem VARCHAR (3), PRIMARY KEY (ID));

--------------------------------------------INSERT RECORDS-------------------------------------------------------------------------------
INSERT INTO xWorkTable (W_vMatchRecord,vSystem) VALUES('Pmt1','CPM'),('Pmt2','CPM'),('Pmt3','CPM'),('Pmt4','CPM'),('Pmt5','CPM')
INSERT INTO xNoMatch   (NM_vMatchRecord,vSystem)VALUES('Pmt2','CSE'),('Pmt3','CSE'),('Pmt0','CPM'),('Pmt8','CPM')

--------------------------------------------SELECT RECORDS FOR A QUICK VIEW--------------------------------------------------------------
SELECT * FROM xWorkTable order by W_vMatchRecord
SELECT * FROM xNoMatch order by NM_vMatchRecord

/*--------------------------------------------------------------------------------------------------------------------------------------
	The goal is to DELETE FROM the "xNoMatch" tabled matching “W_vMatchRecord” records = to "NM_vMatchRecord" in the xWorkTable without 
	delteing more records than are in the xWorkTable. 
	
	For example, If the work table has 2 'Pmt2' values and the xNoMatch table has 3 'Pmt2' records, we must only delete 2 'Pmt2' 
	records because the xWorkTable table drives the count of how many matching records will be delted from the xNoMatch table.
	
	The following intersect select statements beautifully come up with the correct records, but we can not use it to delete, insert, or 
	update records. Or, at least I am not aware of how. 
	
	Thank you for reviewing this. 
	Doug Kelley
*/--------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------SELECT RECORDS FOR A QUICK VIEW--------------------------------------------------------------

SELECT n.NM_vMatchRecord, ROW_NUMBER() OVER(PARTITION  BY NM_vMatchRecord ORDER BY NM_vMatchRecord)as RowNum
FROM xNoMatch N
	INTERSECT
SELECT w.w_vMatchRecord, ROW_NUMBER() OVER(PARTITION  BY W_vMatchRecord ORDER BY W_vMatchRecord) as RowNum		
FROM xWorkTable W

Open in new window

awking00

Your code shows a WorkTable and a NoMatch table, but you mention a Match table in your question. What does that refer to? Some more sample data and the table structures that cover all of the criteria for the deletions and your expected results would be of great assistance.

Doug Kelley

ASKER

Sorry for the confusion; The statement should have been as follows:

The goal is to delete from the xNoMatch the same record count of matching records found in the xWorkTable table. For example, if "xNoMatch" contains 3 matching records, and the xWorkTable table has 2 matching records, delete only 2 records from the xNoMatch table.

RESPONSE TO COMMENT:

"Some more sample data and the table structures that cover all of the criteria for the deletions and your expected results would be of great assistance."

I provided script to create the tables and populate them with values sufficient to demonstrate the idea I think. Basically the same record count of data in the xNoMatch.NM_vMatchRecord that matches on the xWorkTable.W_vMartchRecord fields should be deleted.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Doug Kelley

ASKER

Holy Cow! I think that works!! How the heck did you do that?

From my understanding, the common table expression is a temporary record set. So here is my questions:

There are 2 "FROM" statements in your expression following the DELETE FROM syntax. How can I visualize what is going on here? Is that treated as an implied select? Is this an example of recursion?

Usually I use SELECT * FROM ... prior to DELETE FROM... to insure the records I plan to delete are correct. How can I perform a select on the cte_N prior to deletion? Would it be sufficient to comment out the first from? It seemed to work.
Here is what I tried:

WITH cte_N AS (
    SELECT NM_vMatchRecord, ROW_NUMBER() OVER(PARTITION BY NM_vMatchRecord ORDER BY NM_vMatchRecord) AS RowNum
    FROM xNoMatch
)
select * FROM cte_N
--FROM cte_N
INNER JOIN (
    SELECT w_vMatchRecord, ROW_NUMBER() OVER(PARTITION BY W_vMatchRecord ORDER BY W_vMatchRecord) AS RowNum
    FROM xWorkTable
) AS W ON
    W.w_vMatchRecord = cte_N.NM_vMatchRecord AND
    W.RowNum = cte_N.RowNum

Open in new window

Scott Pletcher

The second "FROM" in the DELETE is just a gimmick to allow JOINs to follow. In the ANSI standard, you can't use JOINs in a DELETE statement. MS wanted to allow it, but the syntax would be non-standard overall ... unless they made you repeat the FROM, which made it "look like" a SELECT, which allows JOINs. So it's not recursive, it's just a syntax kludge.

Your second statement looks fine for testing the results before running.

Doug Kelley

ASKER

Thank you very much Scott. Many kudos.

awking00

>>I provided script to create the tables and populate them with values sufficient to demonstrate the idea I think. Basically the same record count of data in the xNoMatch.NM_vMatchRecord that matches on the xWorkTable.W_vMartchRecord fields should be deleted. <<
So what should the final result look like given those values?

awking00

I know the answer has been accepted but I would still like to know what the final results of your sample data would look like for my edification. Thanks.

Doug Kelley

ASKER

Master Scott Pletcher, or anyone else who might know,
I have been able to implement the suggestion for the delete functionality using common table expressions and have also been able to use the "select" statements using common table expressions where row number matching is used.

My question now relates to an unexpected result using the same theories for an update statement.
In the following example, when I attempt to update the cte_XWorkTable.W_vMatchRecord, all 4 records are updated rather than the matching 3 records matched by row number.

Am I missing something?

Below just run the Create, Insert, and Select statements to see what I'm talking about. Also, if you need to recreate, the Drop is there to start over. Hope that is helpful.

Thank you for your previous answers everyone. Very helpful!

BEGIN /*DROP TABLES*/
		DROP TABLE xWorkTable,xNoMatch,xMatch	
	END
	
	Begin/* CREATE TABLES, INSERT RECORDS*/
		CREATE TABLE xWorkTable	(ID INT IDENTITY (1,1) NOT NULL, W_vMatchRecord  VARCHAR (20), vSystem		INT, PRIMARY KEY (ID));
		CREATE TABLE xNoMatch	(ID INT IDENTITY (1,1) NOT NULL, NM_vMatchRecord VARCHAR (20), vSystem		INT, PRIMARY KEY (ID));
		
		CREATE TABLE xMatch		(ID INT IDENTITY (1,1) NOT NULL, M_vMatchRecord  VARCHAR (20), iResolution	INT, PRIMARY KEY (ID));	
	END			
	
	BEGIN /*INSERT RECORDS*/
		INSERT INTO xWorkTable (W_vMatchRecord,	vSystem)	 VALUES('Pmt1',76),('Pmt1',76),('Pmt1',76),('Pmt1',76)
		INSERT INTO xNoMatch   (NM_vMatchRecord,vSystem)	 VALUES('Pmt2',75),('Pmt2',75),('Pmt2',75),('Pmt2',75),('Pmt1',75),('Pmt1',76)
		
		INSERT INTO xMatch	   (M_vMatchRecord, iResolution) VALUES('Pmt1',72),('Pmt1',72),('Pmt3',71),('Pmt1',74)					--
		
	END

	Begin /*SELECT STATEMENTS */
		SELECT * FROM xWorkTable	order by W_vMatchRecord;				
		SELECT * FROM xNoMatch		order by NM_vMatchRecord		
		SELECT * FROM xMatch		order by M_vMatchRecord
	END

------------------------------------------------------PROCESS LOGIC---------------------------------------------------				
	BEGIN /*STEP 1 UPDATE WORK WITH SYSTEM 0 
			UPDATE REOCRDS IN THE MATCH TABLE (tblCPM_CSEMatch) THAT HAVE NOW BEEN RESOLVED BY THE SYSTEM (UPDATE RESOLUTION TO SYSTEM)*/
			
		--SET SYSTEM TO 0 WHERE RECORDS MATCH
		WITH cte_xWorkTable AS
			(
				SELECT W_vMatchRecord,vSystem, ROW_NUMBER() OVER(PARTITION BY W_vMatchRecord ORDER BY W_vMatchRecord) AS RowNum
				FROM xWorkTable				
			)
		UPDATE cte_xWorkTable	
			SET W_vMatchRecord = 'X'				
		--SELECT * FROM cte_xWorkTable W
		FROM cte_xWorkTable AS W		
			INNER JOIN
				(SELECT M_vMatchRecord, ROW_NUMBER() OVER(PARTITION BY M_vMatchRecord ORDER BY M_vMatchRecord) AS RowNum
				 FROM xMatch) AS M
			ON W.W_vMatchRecord = m.M_vMatchRecord AND W.RowNum = M.RowNum
			
	END

Open in new window

Scott Pletcher

When you use an alias for a table name, you need to use the alias after the UPDATE command as well:

;WITH cte_xWorkTable AS
      (
            SELECT W_vMatchRecord,vSystem, ROW_NUMBER() OVER(PARTITION BY W_vMatchRecord ORDER BY W_vMatchRecord) AS RowNum
            FROM xWorkTable
      )
UPDATE W --rather than UPDATE cte_xWorkTable
      SET W_vMatchRecord = 'X'
--SELECT * FROM cte_xWorkTable W
FROM cte_xWorkTable AS W
      INNER JOIN
            (SELECT M_vMatchRecord, ROW_NUMBER() OVER(PARTITION BY M_vMatchRecord ORDER BY M_vMatchRecord) AS RowNum
             FROM xMatch) AS M
      ON W.W_vMatchRecord = m.M_vMatchRecord AND W.RowNum = M.RowNum

Doug Kelley

ASKER

Oh my gosh! It's a miracle LOL. Thanks Scott!! I owe you BIG TIME! It now makes sense.
Is there a way to award more points? new to this app.

Scott Pletcher

No, e-e doesn't allow extra points. But appreciate it. It's all good as long as you got the code you need.