Need some help with a DELETE statement...

Hi all,

I am trying to delete data from a table where one of the column entries does not having matching data in another table, i.e.

Using the following tables, with test data:

CREATE TABLE TBLLOG ( TEXTDATA VARCHAR(20) COLLATE NONE,  THEID INTEGER);

INSERT INTO TBLLOG (TEXTDATA, THEID)  VALUES ('A', 1);
INSERT INTO TBLLOG (TEXTDATA, THEID)  VALUES ('B', 2);
INSERT INTO TBLLOG (TEXTDATA, THEID)  VALUES ('C', 3);
INSERT INTO TBLLOG (TEXTDATA, THEID)  VALUES ('D', 4);

CREATE TABLE TBLLEAVEMEALONE ( ID INTEGER,  LOGID INTEGER);

INSERT INTO TBLLEAVEMEALONE  (ID, LOGID)  VALUES (1, 2);
INSERT INTO TBLLEAVEMEALONE  (ID, LOGID)  VALUES (2, 4);

Open in new window


I would like to execute a delete on tblLog, leaving any matching data contained in tblLeaveMeAlone.
In the example data, this would leave rows containing "B" and "D"

I'm sure there is a simple query, but it's driving me mad!

It is actually for Interbase, but I would have thought the query would be fairly generic.

Any help would be appreciated.

Thanks,

James
LVL 4
James AtkinSenior Principle Software EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:
Hi,

try this

DELETE FROM TBLLOG WHERE NOT EXISTS  (SELECT * FROM TBLLEAVEMEALONE  WHERE TBLLOG.THEID = TBLLEAVEMEALONE.LOGID)

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
HOLY GOD MAKE A BACKUP FIRST, then give this a whirl..
DELETE FROM l
FROM TBLLOG l
-- LEFT JOIN means delete all from TBLLOG..
   LEFT JOIN TBLLEAVEMEALONE lma ON l.THEID = lma.LOGID
--- .. only for non matching rows
WHERE lma.LOGID IS NULL

Open in new window


btw I have an article out there called SQL Server Delete Duplicate Rows Solutions that you may find useful.

<edited after original submission>
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
DELETE FROM TBLLOG 
WHERE NOT EXISTS (SELECT 1 FROM TBLLEAVEMEALONE  WHERE TBLLOG.THEID = TBLLEAVEMEALONE.LOGID)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

James AtkinSenior Principle Software EngineerAuthor Commented:
Thanks everyone for their really fast input!
I have accepted Vitor's comment as the answer as the syntax is so simple and works great.
Thanks again,
James
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Any chance you can look at earlier comments before accepting a later one?    Especially when they are correct answers, and definitely when the one you accepted is virtually the same as the first comment, inadvertent I'm sure.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
First one won't work since NOT IN needs only one column to be returned.
Jim, yours gave a column error. Shouldn't be lma.THEID but lma.LOGID.  Apart of that it should work.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Correct on both counts.  That's works then, disregard my earlier comment.
0
James AtkinSenior Principle Software EngineerAuthor Commented:
Hi,
I meant no offence by accepting the one I did.
I did try the first comment, but the original syntax "NOT IN" did not work.
I was notified at the same time of both yours and Vitor's comment, and simply accepted the one with the simplest syntax
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Vitor is correct that his was the first correct answer, so the awarding of points is good.    Also it's EE protocol to at least acknowledge earlier comments rather than ignoring them and accepting a later one, so if you would have said something to the effect of 'I tried your comments and they didn't work...' then that would have been fine.   Otherwise without the acknowledgement it looks arbitrary, which will annoy experts.

We're good here.
0
James AtkinSenior Principle Software EngineerAuthor Commented:
You are absolutely right, apologies to both yourself and Lokesh.
I will be more thoughtful in my response next time.
Best regards,
James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.