Insert data from one table to another only if it doesn't already exist

Question

I have the following MySQLi code for inserting column data from one table to another which is working absolutely fine. However what I would like it to do is to insert the column data only if it doesn't already exist in the check_in table. My code is as follows:

$insertInvRoom = $db->prepare("INSERT checkin_rooms (checkin_inventory_id, checkin_room_name, checkin_inventory_room_id) SELECT inventory_id, inventory_room_name, inventory_room_id FROM inventory_rooms WHERE inventory_id = ?");
$insertInvRoom->bind_param("i", $inventoryID[$key]);
$insertInvRoom->execute();
$insertInvRoom->close();

How can I do this?

hichris123 · Answer 1 · 2014-02-06T23:35:31.013

2

You can use the IF NOT EXISTS clause, like this:

$insertInvRoom = $db->prepare("IF NOT EXISTS (SELECT * FROM checkin_rooms WHERE checkin_inventory_id = ?) INSERT INTO checkin_rooms (checkin_inventory_id, checkin_room_name, checkin_inventory_room_id) SELECT inventory_id, inventory_room_name, inventory_room_id FROM inventory_rooms WHERE inventory_id = ?");
$insertInvRoom->bind_param("i", $inventoryID[$key], $inventoryID[$key]);

edited Feb 06 '14 at 23:35

answered Feb 06 '14 at 23:16

hichris123

9,735
15
51
66

Oh as simple as that! That's something totally new to me so thank you for being so quick to answer :) – Andy Holmes Feb 06 '14 at 23:18
@AndyHolmes You're welcome. :P I believe that I made the code actually work now, if I'm correct you're using the `SELECT` at the end to add in data? – hichris123 Feb 06 '14 at 23:19
Ah i've just run this code, turns out it actually stops the data being added to the database all together. Any idea why? – Andy Holmes Feb 06 '14 at 23:22
@AndyHolmes Check if you're using the code in my post now (I edited it around the time I last commented). I think I messed up part of your query at the beginning. – hichris123 Feb 06 '14 at 23:23
Nope, literally adding nothing to the table – Andy Holmes Feb 06 '14 at 23:25
I'm not sure that `IF NOT EXISTS` works for rows, I think it only works for tables and the like. See this http://stackoverflow.com/a/1361368/130691 – JMTyler Feb 06 '14 at 23:27
@AndyHolmes Check it now, I think it was just because it wasn't binding the Id variable to both `?`'s. – hichris123 Feb 06 '14 at 23:29
Ack, i thought it was better, turns out i hadn't cleared the DB. Still nothing – Andy Holmes Feb 06 '14 at 23:33
@AndyHolmes Hmm... seems like I might have missed a `INTO` in the `INSERT INTO`. Try it now. – hichris123 Feb 06 '14 at 23:36
It's still not inserting the data mate. Looks like it just fails entirely – Andy Holmes Feb 06 '14 at 23:38
@AndyHolmes Could you set up an [SQLFiddle](http://sqlfiddle.com/) with your table schema so I can test it? – hichris123 Feb 06 '14 at 23:42
Don't know if that will work mate, theres a lot of php code behind the scenes that triggers what goes where. My original code is working fine. I just need it to not INSERT checkin_rooms (checkin_inventory_id, checkin_room_name, checkin_inventory_room_id) if they already exist based on the data handled by $inventory[$key] – Andy Holmes Feb 06 '14 at 23:44
Not trying to be awkward by the way, there's just a lot of other code. This is just the only bit i need help with. Insert the rows as normal, however if they already exist, dont add them again – Andy Holmes Feb 06 '14 at 23:50

Rida BENHAMMANE · Accepted Answer · 2014-02-06T23:36:37.173

1

You can use NOT EXISTS like this :

$insertInvRoom = $db->prepare("INSERT INTO checkin_rooms (checkin_inventory_id, checkin_room_name, checkin_inventory_room_id) SELECT i.inventory_id, i.inventory_room_name, i.inventory_room_id FROM inventory_rooms i WHERE i.inventory_id = ? " +
    " AND NOT EXISTS (SELECT * FROM checkin_rooms cr WHERE cr.checkin_inventory_id = i.inventory_id)");
$insertInvRoom->bind_param("i", $inventoryID[$key]);
$insertInvRoom->execute();
$insertInvRoom->close();

edited Feb 06 '14 at 23:36

answered Feb 06 '14 at 23:28

Rida BENHAMMANE

4,073
1
12
25

Still adds nothing to the table – Andy Holmes Feb 06 '14 at 23:29
It's not because you have already added the row you test with ? – Rida BENHAMMANE Feb 06 '14 at 23:30
No the database is empty – Andy Holmes Feb 06 '14 at 23:31
Still nothing happening. The table is empty – Andy Holmes Feb 06 '14 at 23:39
No I copy/past the query in my answer – Rida BENHAMMANE Feb 06 '14 at 23:52
Yep that seems to be working. Is there any chance you can help me with another if we go into chat quickly? It's a little longer than this one – Andy Holmes Feb 06 '14 at 23:55
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/46974/discussion-between-rida-benhammane-and-andy-holmes) – Rida BENHAMMANE Feb 06 '14 at 23:56

score 0 · Answer 3 · answered Feb 06 '14 at 23:26

I know you've already got your answer, but just in case you're interested, there's another type of query you can do to insert data if it doesn't exist, or update it if it does. For example:

INSERT INTO my_table (my_column1, my_column2, my_count)
    VALUES ('some string', 14, 1)
ON DUPLICATE KEY
    UPDATE my_column1 = 'some_string', my_column2 = 14, my_count = my_count + 1;

Probably doesn't help you this time around, but it's super useful if you just want to make sure the row is there, whether it was already there or not, without doing multiple queries.

For more info: http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

But it wouldnt need to update, it just doenst need to insert it if its already there :) — Andy Holmes, Feb 06 '14 at 23:28
Yes, I know :-) Just thought you might find it useful to know down the road, since it's a related concern — JMTyler, Feb 06 '14 at 23:30

Insert data from one table to another only if it doesn't already exist

3 Answers3