Solved

sql syntax

Posted on 2016-08-29
2
60 Views
Last Modified: 2016-08-29
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
Comment
Question by:amucinobluedot
2 Comments
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 41775554
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
 

Author Comment

by:amucinobluedot
ID: 41775573
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to count occurrences of each item in an array.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now