Delete statement SQL joins

Question

Can anyone please tell me how to write a delete statement for the following query:

      SELECT     a.UserID, b.UserEmailAddress
      FROM  tblUserProgramme AS a LEFT OUTER JOIN

      tblUser AS b ON b.UserID = a.UserID LEFT OUTER JOIN
      tblWorkGroup AS c ON c.WorkGroupID = b.WorkGroupID

      WHERE(a.ProgrammeID = 59) AND (a.UserID NOT IN

          (SELECT     UserID FROM tblUser AS a WHERE (WorkGroupID IN
             (SELECT     WorkGroupID FROM tblWorkGroup WHERE                          
             (WorkGroupName  LIKE  '%Insight%') OR (WorkGroupName LIKE '%Other%'))))) 

      AND (b.UserEmailAddress NOT IN
            (SELECT     email FROM          tmpUsers))

      ORDER BY b.UserEmailAddress

SERVER is SQL Server 2005

which database? SQL server, mysql?

Joe G Joseph
– Joe G Joseph

2012-09-06 09:36:44 +00:00
Commented Sep 6, 2012 at 9:36 — Joe G Joseph
– Joe G Joseph, Commented Sep 6, 2012 at 9:36
have edited the question

rosebrit3
– rosebrit3

2012-09-06 09:45:34 +00:00
Commented Sep 6, 2012 at 9:45 — rosebrit3
– rosebrit3, Commented Sep 6, 2012 at 9:45
Confusing.... could you please add bit more detail?

Manish Prajapati
– Manish Prajapati

2012-09-06 09:45:39 +00:00
Commented Sep 6, 2012 at 9:45 — Manish Prajapati
– Manish Prajapati, Commented Sep 6, 2012 at 9:45

Manish Prajapati · Accepted Answer · 2012-09-06 09:44:35Z

4

Query would be

Delete from 

 ( 
 SELECT     a.UserID, b.UserEmailAddress
  FROM  tblUserProgramme AS a LEFT OUTER JOIN
  tblUser AS b ON b.UserID = a.UserID LEFT OUTER JOIN
  tblWorkGroup AS c ON c.WorkGroupID = b.WorkGroupID
  WHERE(a.ProgrammeID = 59) AND (a.UserID NOT IN
(SELECT     UserID FROM tblUser AS a WHERE (WorkGroupID IN
(SELECT     WorkGroupID FROM tblWorkGroup WHERE (WorkGroupName  LIKE           
'%Insight%') OR (WorkGroupName LIKE '%Other%'))))) AND (b.UserEmailAddress NOT IN
(SELECT     email FROM          tmpUsers))
ORDER BY b.UserEmailAddress
) a

answered Sep 6, 2012 at 9:44

Manish Prajapati

5852 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe G Joseph · Accepted Answer · 2012-09-06 09:38:50Z

3

Syntax is DB specific..
For most of tha databases this should work , if you want to delete from tblUserProgramme

DELETE A
FROM tblUserProgramme AS a 
 .....

answered Sep 6, 2012 at 9:38

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Comments

StuartLC · Accepted Answer · 2012-09-06 09:50:51Z

2

In general, in SQL Server, you can delete all records which match a given SQL query as follows, provided your SELECT only returns columns from a single table:

DELETE x
FROM
(
   -- Any query which returns data from a single table, that you wish to delete
) x;

Or, using a CTE:

;WITH x as 
(
   -- Any query which returns data from a single table, that you wish to delete
)
DELETE x;

answered Sep 6, 2012 at 9:50

StuartLC

108k18 gold badges224 silver badges293 bronze badges

Comments

rosebrit3 · Accepted Answer · 2012-09-06 10:09:10Z

Thank you all for your quick answers:) There were a quite a few answers right, but had to pick the first one:)

As Joe G Joseph has mentioned, this is what i did

DELETE a
from dbo.tblUserProgramme a
LEFT OUTER JOIN tblUser b ON b.UserID = a.UserID
LEFT OUTER JOIN tblWorkGroup c ON c.WorkGroupID = b.WorkGroupID
where a.ProgrammeID = 59 AND 
a.UserID  NOT IN (SELECT UserID  FROM tblUser a WHERE a.WorkGroupID IN
(SELECT WorkGroupID FROM tblWorkGroup
WHERE WorkGroupName like '%Insight%' OR WorkGroupName like '%Other%')) 
 AND b.UserEmailAddress NOT IN(SELECT email FROM tmpUsers)

Collectives™ on Stack Overflow

Delete statement SQL joins

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related