CODEWITHSUNDEEP
mysqljoinmariadbsql-deletemariasql
Bemoretechnical
Bemoretechnical

Reputation: 1

Delete row from one table based off another table

I have been trying to 3 days but can’t figure it out

I have two tables

One table called se with 12 columns and 1 unique ID. One column is npcid and it is not unique in this table.

There’s another table called npcs where there is a unique id. The unique id from this table can appear multiple times in the se table.

I need to delete every entry from the se table based off of the id in the npc table. I want delete every row in se where the npcid is not listed on the npc table. I have been researching for days and can’t seem to figure it out. I’ve been trying the join command

I am using sql 7.4.3 10.3.37-MariaDB-0ubuntu0.20.04.1 and I did download the npc table and just copied the ids and was able to use DELETE FROM se WHERE npcid NOT IN (100, 200, 300);

But I noticed it was missing items probably due the fact it is over 1,000,000 entries and over 50,000 exclusions.

Any help would be great as I am really struggling with this join command, I can’t even get someone to give me a simple example. I feel like it shouldn’t be that hard to delete data based on data in another table but I’m just learning and I’m sure it is simmering I’m obviously doing wrong

I have tried over 300 commands, I have been reading and researching for about 3 days I’ve tried manually coping the ids and using a different command but it is missing data

I was pointed to an answer which has the opposite effect, I do not want to delete the ids based on the ids in the other table but rather those are the ids I want to keep

Upvotes: 0

Views: 66

Answers (1)

Bemoretechnical
Bemoretechnical

Reputation: 1

I did eventually figure it out, I’m not sure why I was having so many issues but what I did was

DELETE
FROM table 
WHERE column2 NOT IN
(SELECT column2 FROM npc);

Upvotes: 0

Related Questions