CODEWITHSUNDEEP
mysqlsql
varadharajan p.r Rajan
varadharajan p.r Rajan

Reputation: 71

delete one row by Specific ID which the same is not present another table Column ID

mysql> select *from add_member;
+-----------+-------------+--------------+-------------------+
| member_ID | member_name | member_phone | member_department |
+-----------+-------------+--------------+-------------------+
|     12345 | kishore     | 89482934     | IT738             |
|     12347 | ram         | 783892389    | IT721829          |
|    12346 | vardhan     | 902112       | TI5738            |

mysql> select *from ownbook;


    +--------+----------+-----------+-------------+------------+-----------

    | BookID | Bookname | member_ID | member_name | from       | to         |
    +--------+----------+-----------+-------------+------------+------------+
    |   1006 | physics  |     12346 | ram         | 2016-07-05 | 2016-07-20 |
    +--------+----------+-----------+-------------+------------+------------+
1 row in set (0.00 sec)

Upvotes: 0

Views: 43

Answers (2)

miiiii
miiiii

Reputation: 1610

If you want to delete one specific id if and only if it shouldn't be there in second table then you can do this:

delete from add_member where member_ID='SEPCIFIC_ID_YOU_WANT' and member_ID not in(select member_ID from ownbook)

Working 100% tested.

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can try like this:

delete from add_member where member_ID  not exists(select member_ID from ownbook)

or you can use NOT IN like

delete from add_member where member_ID  not in(select member_ID from ownbook)

If it is specific data then simply do:

delete from add_member 
where member_ID  not in(select member_ID from ownbook where member_id in(12345,12347))

Upvotes: 1

Related Questions