MySQL insert into table with values and where

Question

I have two tables event and guest and an eventGuest table that joins them together and has some information about the guest (like if they attended etc) and am trying to insert into the eventGuest table without creating a duplicate sort of like:

insert into eventGuest(eventID, GuestID, attended) 
    values(iEventID, iGuestID, bAttended) 
where (select count(*) from eventGuest where eventID = iEventID and guestID = iGuestID) = 0

Link worked but I needed to alias the columns in the temp table for when the IDs were (coincidentally) the same — matts1189, Oct 22 '15 at 09:30

score 0 · Answer 1 · answered Oct 22 '15 at 09:14

0

Copy one table data to another :-

INSERT INTO TARGET_TABLE (`col1`,`col2`) SELECT `col1`,`col2` FROM SOURCE_TABLE;

answered Oct 22 '15 at 09:14

Abhishek Sharma

6,523
1
12
20

score 0 · Answer 2 · answered Oct 22 '15 at 09:16

You should use INSERT INTO ... SELECT if you want to insert values from a table into another:

INSERT INTO eventGuest(eventID, GuestID, attended) 
SELECT iEventID, iGuestID, bAttended
FROM Anothertable t
where NOT EXIST(select 1 
                from eventGuest e
                where e.eventID = t.iEventID 
                  and e.guestID = t.iGuestID);

Or, if you want to insert into the same table if the values of eventid and iGuestid doesn't exist, you can do this:

INSERT INTO eventGuest(eventID, GuestID, attended)
SELECT * 
FROM ( SELECT 'eventid', 'guestid', 'somevalue' ) AS t
WHERE NOT EXISTS (
    SELECT 1 FROM eventGuest e
           WHERE e.eventID ='eventid'
           and e.guestID = 'guestid'
) LIMIT 1;

score 0 · Answer 3 · answered Oct 22 '15 at 09:40

0

Please do add a unique constraint in the eventGuest table for both eventid and guestid and use INSERT IGNORE or REPLACE command to insert the new data.

answered Oct 22 '15 at 09:40

Adarsh Gangadharan

583
3
10

MySQL insert into table with values and where

3 Answers3