0

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

Bemoretechnical
  • 1
  • 3
  • *I have two tables* Read [Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055) and provide this according to #5. *I am using sql 7.4.3* ??? Provide complete output for `SELECT VERSION();`. *I want delete every row in se where the npcid is not listed on the npc table.* Use WHERE NOT EXISTS. – Akina Feb 10 '23 at 04:35
  • Does this answer your question? [Delete all rows in a table based on another table](https://stackoverflow.com/questions/1590799/delete-all-rows-in-a-table-based-on-another-table) – Georg Richter Feb 10 '23 at 05:33

1 Answers1

0

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);
buddemat
  • 4,552
  • 14
  • 29
  • 49
Bemoretechnical
  • 1
  • 3