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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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

Jim HornMicrosoft SQL Server Data DudeCommented:
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>
Vitor MontalvãoMSSQL Senior EngineerCommented:
DELETE FROM TBLLOG 
WHERE NOT EXISTS (SELECT 1 FROM TBLLEAVEMEALONE  WHERE TBLLOG.THEID = TBLLEAVEMEALONE.LOGID)

Open in new window

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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
Jim HornMicrosoft SQL Server Data DudeCommented:
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.
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.
Jim HornMicrosoft SQL Server Data DudeCommented:
Correct on both counts.  That's works then, disregard my earlier comment.
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
Jim HornMicrosoft SQL Server Data DudeCommented:
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.
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
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.