[Webinar] Streamline your web hosting managementRegister Today

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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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