What is Error Corrent SQL>!!!

Question

1.00/5 (1 vote)

See more:

SQL

DELETE FROM 
ledger l 
LEFT JOIN Party p on p.Id = l.Account_Id OR p.Id = l.ContraAccount_Id
LEFT JOIN AcGroup acg ON acg.Id = p.AcGroup_Id
WHERE acg.Id is NULL

Shows an error
"Incorrect syntax near 'l'. Severity 15"

Posted 28-Feb-12 23:35pm

Photon_

Updated 1-Mar-12 9:06am

fjdiewornncalwe

v7

Add a Solution

Comments

OriginalGriff 29-Feb-12 5:37am

Do you get an error message?
Or does something happen that shouldn't?

Photon_ 29-Feb-12 5:54am

i got error
Incorrect syntax near 'l'. Severity 15

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2012-02-29T00:17:00

Solution 2

I assume that your tables name is "ledger l"?
Try delimiting it:

SQL

DELETE FROM 
[ledger l]
LEFT JOIN Party WHERE p.Id = l.Account_Id OR p.Id = l.ContraAccount_Id
LEFT JOIN AcGroup acg ON acg.Id = p.AcGroup_Id
WHERE acg.Id is NULL

The problem could well be that the space in the name is being treated as the name end.

Posted 29-Feb-12 0:17am

OriginalGriff

Comments

Photon_ 29-Feb-12 7:51am

gives error Incorrect syntax near the keyword 'LEFT'. Severity 15

OriginalGriff 29-Feb-12 8:52am

Ah! Take teh delimiters back out - it's more complex than that!
Have a look at this blog. It does what you want but the syntax is a little odd to my mind:
http://www.bennadel.com/blog/939-Using-A-SQL-JOIN-In-A-SQL-DELETE-Statement-Thanks-Pinal-Dave-.htm

MaulikDusara · Accepted Answer · 2012-02-29T19:47:00

Solution 6

Hi,
Find you solution here:

SQL

DELETE FROM ledger
WHERE Account_ID IN (SELECT id FROM Party LEFT JOIN AcGroup ON AcGroup.ID = Party.AcGroup_ID AND AcGroup.Id IS NULL)
AND ContraAccount_Id IN (SELECT id FROM Party LEFT JOIN AcGroup ON AcGroup.ID = Party.AcGroup_ID AND AcGroup.Id IS NULL)

I don't know the connection between Account_id and ContractAccount_id relationship.

Posted 29-Feb-12 19:47pm

MaulikDusara

Comments

Photon_ 1-Mar-12 2:09am

both are connected to party.id

[no name] 1-Mar-12 4:11am

yes

Om Prakash Pant · Accepted Answer · 2012-02-28T23:42:00

Solution 1

i think the query should be something like

SQL

DELETE FROM
ledger l
JOIN Party ON p.Id = l.Account_Id 
JOIN Party ON p.Id = l.ContraAccount_Id
LEFT JOIN AcGroup acg ON acg.Id = p.AcGroup_Id
WHERE acg.Id is NULL

Before deleting the contents check the data by using the Select statement. If the records are correct then execute the delete statement..

Posted 28-Feb-12 23:42pm

Om Prakash Pant

Comments

Photon_ 29-Feb-12 5:45am

see improved question error comes near 'l'

Alberto Flores · Accepted Answer · 2012-02-29T02:53:00

Solution 3

DELETE FROM
ledger l
LEFT JOIN Party p WHERE p.Id = l.Account_Id OR p.Id = l.ContraAccount_Id
LEFT JOIN AcGroup acg ON acg.Id = p.AcGroup_Id
WHERE acg.Id is NULL

Posted 29-Feb-12 2:53am

Alberto Flores

Comments

Photon_ 1-Mar-12 0:22am

gives error
Incorrect syntax near 'l'. Severity 15

AmitKumar89 · Accepted Answer · 2012-02-29T18:52:00

Solution 4

Hi there..

Try this..

SQL

DELETE l FROM
ledger  l
LEFT JOIN Party p ON p.Id = l.Account_Id OR p.Id = l.ContraAccount_Id
LEFT JOIN AcGroup acg ON acg.Id = p.AcGroup_Id
WHERE acg.Id is NULL

all the best..

Posted 29-Feb-12 18:52pm

AmitKumar89

Comments

Photon_ 1-Mar-12 2:10am

Great good work

rajdeep kumar · Accepted Answer · 2012-02-29T19:32:00

Solution 5

hi findz..
in ur query there is no alias name table party and u r using 'p' as alias in query so first correct it and use ON before WHERE clause, try this may this is ur answer

DELETE l FROM ledger l
LEFT JOIN Party p ON p.Id = l.Account_Id OR p.Id = l.ContraAccount_Id
LEFT JOIN AcGroup acg ON acg.Id = p.AcGroup_Id
WHERE acg.Id is NULL

plz revert back with ur comments.....

Posted 29-Feb-12 19:32pm

rajdeep kumar

Updated 29-Feb-12 19:34pm

v2

Comments

Photon_ 1-Mar-12 2:13am

you were right but the main mistake was
delete 'l'

i was not adding this 'l' thanks