Need help with Delete - 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

Need help with Delete

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-08 : 16:30:11

If this produces 1 record:

SELECT *
FROM    SOModifications.dbo.SOMODTEST AS MOD
        INNER JOIN ( SELECT fsono,
                            fenumber,
                            frelease,
                            fmasterrel
                     FROM   SORELS
                     WHERE  fmasterrel = 1
                   ) AS DS ON MOD.fsono = DS.fsono
                              AND MOD.fenumber = DS.fenumber
                              AND MOD.frelease = DS.frelease

How do I then delete the record from SOModifications.dbo.SOMODTEST that this query returns? What is the syntax to embed this?

pootle_flump

1064 Posts

Posted - 2008-08-08 : 16:37:30

Ok - maybe I am being dumb but swap SELECT * with DELETE SOModifications.dbo.SOMODTEST

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-08 : 16:39:07

Actually, this works:

DELETE  FROM SOModifications.dbo.somodtest
WHERE   SOModifications.dbo.somodtest.frelease = '000'
        AND EXISTS ( SELECT fsono
                     FROM   M2MData01.dbo.sorels
                     WHERE  fmasterrel = 1 )

::smacks forehead:: I wasn't thinking sets again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-09 : 01:58:48

or this

DELETE MOD
FROM    SOModifications.dbo.SOMODTEST AS MOD
        INNER JOIN ( SELECT fsono,
                            fenumber,
                            frelease,
                            fmasterrel
                     FROM   SORELS
                     WHERE  fmasterrel = 1
                   ) AS DS ON MOD.fsono = DS.fsono
                              AND MOD.fenumber = DS.fenumber
                              AND MOD.frelease = DS.frelease

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources