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

Doug KelleySenior Programmer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Commented:
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.
0
Doug KelleySenior Programmer AnalystAuthor Commented:
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.
0
Scott PletcherSenior DBACommented:
I suppose one could find a way to do this with INTERSECT, but I think a cte is much easier to write and understand:

;WITH cte_N AS (
    SELECT NM_vMatchRecord, ROW_NUMBER() OVER(PARTITION BY NM_vMatchRecord ORDER BY NM_vMatchRecord) AS RowNum
    FROM xNoMatch
)
DELETE 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


Btw, just a side note.  Which specific rows match is effectively random, since you sorted only on MatchRecord.  If you want to match "first" or "last" rows, you can add ", ID" or ", ID DESC", respectively, to the ORDER BY clause of the ROW_NUMBER().  That will give you consistent results if you re-run the query, such as during testing, for example.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Doug KelleySenior Programmer AnalystAuthor Commented:
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

0
Scott PletcherSenior DBACommented:
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.
0
Doug KelleySenior Programmer AnalystAuthor Commented:
Thank you very much Scott. Many kudos.
0
awking00Commented:
>>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?
0
awking00Commented:
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.
0
Doug KelleySenior Programmer AnalystAuthor Commented:
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

0
Scott PletcherSenior DBACommented:
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
0
Doug KelleySenior Programmer AnalystAuthor Commented:
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.
0
Scott PletcherSenior DBACommented:
No, e-e doesn't allow extra points.  But appreciate it.  It's all good as long as you got the code you need.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.