Delete using join not working

[PeDa]

I want to move records from one table (tbConstateringen) to another (tbArcConstateringen) by inserting into the first and then deleting from the second, based on a value in table tbStudies which is linked (1 to many) to table tbAudits, which is linked (1 to many) to table tbConstateringen.
The updating works:

SET IDENTITY_INSERT dbo.tbArcConstateringen ON
INSERT INTO tbArcConstateringen 
  (
  coNummer_i,coTYPE_i,coConstatering,coStatus_i,coWissen_b,coAuditkey,coAntwoord,coKeyfield
  )
  SELECT 
  coNummer_i,coTYPE_i,coConstatering,coStatus_i,coWissen_b,coAuditkey,coAntwoord,coKeyfield
  FROM  dbo.tbConstateringen LEFT OUTER JOIN dbo.tbAudits 
  ON dbo.tbConstateringen.coAuditkey = dbo.tbAudits.aKeyfield LEFT OUTER JOIN dbo.tbStudies 
  ON dbo.tbAudits.aStudyKey = dbo.tbStudies.gKeyfield
  WHERE (dbo.tbStudies.gKeyfield=@record)
print @@rowcount
SET IDENTITY_INSERT dbo.tbArcConstateringen OFF

The deleting always removes 0 records:

DELETE  tbConstateringen FROM  dbo.tbConstateringen LEFT JOIN dbo.tbAudits 
  ON dbo.tbConstateringen.coAuditkey = dbo.tbAudits.aKeyfield LEFT JOIN dbo.tbStudies 
  ON dbo.tbAudits.aStudyKey = dbo.tbStudies.gKeyfield
  WHERE (dbo.tbStudies.gKeyfield=@record)
print @@rowcount

Any suggestions welcome

 

[gmmastros]

Have you tried running a select statement to see if there are any matches to your query?

SELECT  tbConstateringen.* FROM  dbo.tbConstateringen LEFT JOIN dbo.tbAudits 
  ON dbo.tbConstateringen.coAuditkey = dbo.tbAudits.aKeyfield LEFT JOIN dbo.tbStudies 
  ON dbo.tbAudits.aStudyKey = dbo.tbStudies.gKeyfield
  WHERE (dbo.tbStudies.gKeyfield=@record)

By the way, you have a left join query, but you are filtering on one of the "left" tables, so this is actually acting like an inner join. Could this be your issue?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[denimined]

I can't see anything with the delete statement, but like gmmastros says, the joins shouldn't be LEFT's (you are just slowing the queries down). Also, the second join is redundant, since the join condition is also the WHERE condition.

e.g.

DELETE   tbConstateringen 
FROM dbo.tbConstateringen AS TC
JOIN dbo.tbAudits  AS TA ON TA.aKeyfield = TC.coAuditkey 
JOIN dbo.tbStudies AS TS ON TS.gKeyfield = TA.aStudyKey 
WHERE (TS.gKeyfield = @record)

Is equivalent to

DELETE   tbConstateringen 
FROM dbo.tbConstateringen as TC
JOIN dbo.tbAudits as TA ON TA.aKeyfield = TC.coAuditkey 
WHERE (TA.aStudyKey = @record)

 

[PeDa]

Thank you for these inputs. I see that the queries can be improved, but the strange thing (to me) is that the criteria for both the INSERT and DELETE queries are the same, but the INSERT does find data (the printed value of @@rowcount is correct), but the DELETE doesn'n, which means that after running both queries I now have two copies of the records I am trying to move (in the original tbConstateringen and in tbArcConstateringen, to which I am trying to move them).
Peter D.

 

[gmmastros]

Do you have any triggers on the table? Please run this and post back with the output.

sp_helptrigger 'tbConstateringen'



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PeDa]

I don't have any triggers, so sp_helptrigger didn't show anything.

Upon further investigation I have come up with the following, which does work:

UPDATE tbConstateringen
  SET coWissen_b = 1
  FROM  dbo.tbConstateringen LEFT OUTER JOIN dbo.tbAudits 
  ON dbo.tbConstateringen.coAuditkey = dbo.tbAudits.aKeyfield LEFT OUTER JOIN dbo.tbStudies 
  ON dbo.tbAudits.aStudyKey = dbo.tbStudies.gKeyfield
  WHERE (tbStudies.gKeyfield=@record)
PRINT @@rowcount
DELETE FROM tbConstateringen 
  WHERE coWissen_b=1
PRINT @@rowcount

It was coded like this in the old MS Access application I am in the process of converting, and the boolean field coWissen_b seems to be here just for that purpose ("wissen"= delete in Dutch).

Incidentally the second join to table tbStudies is actually necessary; my example "WHERE (tbStudies.gKeyfield=@record)" was to simplift things; the actual criterium is another field in tbStudies.

Peter D.

 

[PeDa]

Upon even further investigation, I regret to say I have been leading you on a wild goose chase! My code is part of a procedure to archive a number of tables with a key in tbStudies, and I was removing the records from tbAudits before attempting to remove them from tbConstateringen. If I reverse the order, my original code works as it should. Sorry for my premature call for assistance (which was due to not looking at the original Access code critically enough).

Peter D.