How to do the Insert if the IDs shot exists in different Table in Mysql?

Asked Oct 03 '14 at 05:13

Active Oct 03 '14 at 15:51

Viewed 34 times

Ok, Here is my table

Member table
memberID
1
2

BadMember table
memberID
1
5
memebrID from BadMember
Action table
memberID - otherfiel..

I want only NON-bad member can be inserted into Action.

So, if insert into Action(memberID) values(1) then the system will reject

So, here is what I tried

insert into Action(memberID) select 1 from Member where 1 not in (select memberID from BadMember)

But it doesnt works.

Can u help to fix it?

asked Oct 03 '14 at 05:13

Tum

Use a `BEFORE INSERT` trigger, and signal an error if the row exists in the other table. See http://stackoverflow.com/questions/2538786/how-to-abort-insert-operation-in-mysql-trigger – Barmar Oct 03 '14 at 05:19
why we need trigger? the trigger is the last thing we should think of – Tum Oct 03 '14 at 05:25
@Barmar, I found. insert into Action(memberID) select 1 from Member where memberID=1 and memberID not in (select memberID from BadMember) – Tum Oct 03 '14 at 05:47
I misunderstood. I thought you wanted to prevent someone from inserting these row, not put a check into your own insert query. – Barmar Oct 03 '14 at 15:51

0 Answers0