Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to delete all rows if ID not exist in same table

I have a table in mysql like this:

id userID idRef userIDRef
1 30 2 100
2 100 0 0
3 30 6 100
5 30 7 100
7 100 0 0
10 30 8 100

In php I want to remove all rows for userID = 30 where userIDRef = 100 and idRef not exist in table. In this example I want to remove rows with idRef = 6 and idRef = 8 because not exist inside table

DELETE FROM table
WHERE userID = 30
  AND idRef != 0
  AND idRef NOT IN (
    SELECT id
    FROM table
    WHERE userID = 100
    AND)
Tags:

2

Answers


  1. 0

    Create test data:

    DROP TABLE if EXISTS _tmpTable;
CREATE TEMPORARY TABLE _tmpTable (
    id INT,
    userID INT,
    idRef INT,
    userIDRef INT
);
insert into _tmpTable VALUES
    (1, 30, 2, 100),
    (2, 100, 0,0),
    (3, 30, 6, 100),
    (5, 30, 7, 100),
    (7, 100, 0, 0),
    (10, 30, 8, 100)
;
SELECT * FROM _tmpTable
;

    Now do the deletion:

    DELETE FROM _tmpTable
WHERE 
        userID = 30
    AND UserIDRef = 100
    AND idRef NOT IN (SELECT Id FROM _tmpTable)
;

    … and verify:

    SELECT * FROM _tmpTable;
    Login or Signup to reply.
  2. 0

    See example

    delete
from test  using test 
left join 
  (select distinct id from test)t2 
on test.idRef  =t2.id
  where test.userIdRef=100 and test.userID=30
      and  t2.id is null
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search