MySQL INSERT With SELECT Statement Doing Nothing

Question

I'm trying to insert a row into a table, but only if the combination of the X and Y does not exist in the table already, so I searched around and eventually came up with this query, but it's doing nothing, and throwing no errors.

INSERT INTO `tiles` (`TileID`, `Type`, `X`, `Y`) 
 SELECT UUID(), (FLOOR(RAND() * 4)), 0, 0 FROM `tiles` 
 WHERE NOT EXISTS (SELECT 1 FROM `tiles` WHERE `X`=0 AND `Y`=0) LIMIT 1;

Any help is greatly appreciated.

Possible duplicate of http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table — Kyle, Jul 21 '13 at 03:19
ang how is you `outer select` *connected* with the `select` on `exists` block? — John Woo, Jul 21 '13 at 03:37

score 1 · Answer 1 · answered Jul 21 '13 at 03:40

Essentially, all you have to do it wrap your select with select * from(). This makes your query:

INSERT INTO `tiles` (`TileID`, `Type`, `X`, `Y`) 
SELECT * FROM (SELECT UUID(), (FLOOR(RAND() * 4)), 0 as x, 0 as y) as tmp
WHERE NOT EXISTS (SELECT 1 FROM `tiles` WHERE `X`=0 AND `Y`=0) LIMIT 1;

I had to add the "as x" and "as y" because mysql was complaining about "duplicate column name '0'". My guess is that without the name explicitly written out it just uses the value as the name. Anyway, this query should work. Also, there is more information about this type of stuff in Kyle's link above.

score 1 · Accepted Answer · answered Jul 21 '13 at 05:05

A possible solution might be to add a UNIQUE constraint on (x, y)

ALTER TABLE tiles ADD UNIQUE (x, y);

and use INSERT IGNORE

INSERT IGNORE INTO tiles VALUES (UUID(), (FLOOR(RAND() * 4)), 0, 0);
INSERT IGNORE INTO tiles VALUES (UUID(), (FLOOR(RAND() * 4)), 0, 0);

Here is SQLFiddle demo

MySQL INSERT With SELECT Statement Doing Nothing

2 Answers2