Solved

sql syntax

Posted on 2016-08-29
2
76 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

734 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