Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

sql syntax

I have the following sql to select a number of records. I get a total of 280

SELECT  *
FROM    Activities a
        INNER JOIN Cases AS b ON a.CaseId = b.Id
        INNER JOIN Atts AS d ON d.CaseId = b.Id
        LEFT JOIN Users AS c ON a.responsible = c.UserId
        INNER JOIN Users AS e ON b.AlienId = e.UserId
WHERE   a.FirmId = 9
        AND ( a.ActType = 'TASK'
              OR a.ActType = 'DEADLINE'
            )
        AND a.Complete = '0'
        AND a.DateInitiated < LEFT(DATEADD(DAY, 0, GETDATE()), 12)
        AND b.Archived <> 1
        AND c.UserId is null
ORDER BY c.MailStr ASC;

Open in new window


I now want to delete them so I tried

DELETE
FROM    Activities a
        INNER JOIN Cases AS b ON a.CaseId = b.Id
        INNER JOIN Atts AS d ON d.CaseId = b.Id
        LEFT JOIN Users AS c ON a.responsible = c.UserId
        INNER JOIN Users AS e ON b.AlienId = e.UserId
WHERE   a.FirmId = 9
        AND ( a.ActType = 'TASK'
              OR a.ActType = 'DEADLINE'
            )
        AND a.Complete = '0'
        AND a.DateInitiated < LEFT(DATEADD(DAY, 0, GETDATE()), 12)
        AND b.Archived <> 1
        AND c.UserId is null

Open in new window


But i get this error

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'a'.

Open in new window

0
Aleks
Asked:
Aleks
1 Solution
 
Dan CraciunIT ConsultantCommented:
Try
DELETE  a
FROM    Activities a
        INNER JOIN Cases AS b ON a.CaseId = b.Id
        INNER JOIN Atts AS d ON d.CaseId = b.Id
        LEFT JOIN Users AS c ON a.responsible = c.UserId
        INNER JOIN Users AS e ON b.AlienId = e.UserId
WHERE   a.FirmId = 9
        AND ( a.ActType = 'TASK'
              OR a.ActType = 'DEADLINE'
            )
        AND a.Complete = '0'
        AND a.DateInitiated < LEFT(DATEADD(DAY, 0, GETDATE()), 12)
        AND b.Archived <> 1
        AND c.UserId is null

Open in new window


HTH,
Dan
0
 
AleksAuthor Commented:
Strange, the select returns 280 records.
The delete was that of 89 records. But when I run the select again I get zero results  :$

Is this correct ? the select returns more records because of the inner joins ?
0
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.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now