Why does this Delete fail? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Why does this Delete fail?

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-05-18 : 15:22:19

Is it because of the inner join?

DELETE FROM CheltonCustomizations.dbo.soMods SOMODs
inner join (SELECT SOMOD.fsono, SOMOD.finumber, count(SOMOD.frelease)
                   FROM cheltoncustomizations.dbo.soMods SOMOD
                  group by SOMOD.fsono, SOMOD.finumber
                  having count(SOMOD.frelease) > 1) as temp
                  on SOMODs.fsono = temp.fsono
                  and somods.finumber = temp.finumber
                  where somods.frelease = '000'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-18 : 16:17:54

try this:


DELETE somods
FROM CheltonCustomizations.dbo.soMods SOMODs
inner join (SELECT SOMOD.fsono, SOMOD.finumber, count(SOMOD.frelease)
                   FROM cheltoncustomizations.dbo.soMods SOMOD
                  group by SOMOD.fsono, SOMOD.finumber
                  having count(SOMOD.frelease) > 1) as temp
                  on SOMODs.fsono = temp.fsono
                  and somods.finumber = temp.finumber
                  where somods.frelease = '000'

Be One with the Optimizer
TG

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-05-18 : 17:00:11

Thanks TG, this "works"

DELETE   somods
  FROM      CheltonCustomizations.dbo.soMods SOMODs
         INNER JOIN
            (  SELECT   SOMOD.fsono, SOMOD.finumber
                 FROM   cheltoncustomizations.dbo.soMods SOMOD
             GROUP BY   SOMOD.fsono, SOMOD.finumber
               HAVING   count (SOMOD.frelease) > 1) AS temp
         ON SOMODs.fsono = temp.fsono AND somods.finumber = temp.finumber
 WHERE   somods.frelease = '000'

However, I want to make sure that I am doing this properly.

fsono is a sales order number. finumber is a line item number. frelease is each release per line item number.

I am trying to pull the multi-release (more than 1 release per line item) items only. The final where statement deletes the master release record 000 for those items which are multi-release.

Does this make sense?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-18 : 17:07:47

While you are developing the statement, replace the DELETE with a SELECT somods.*
That way you can safely develop your statement. When you are SELECTing just the rows you want to delete then change back to DELETE.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources