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 stackoverflow.com/questions/3164505/… — Kyle
– Kyle, Commented Jul 21, 2013 at 3:19
ang how is you outer select connected with the select on exists block? — John Woo
– John Woo, Commented Jul 21, 2013 at 3:37

peterm · Accepted Answer · 2013-07-21 05:05:49Z

1

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

answered Jul 21, 2013 at 5:05

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matthew Wesly · Accepted Answer · 2013-07-21 03:40:16Z

1

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.

answered Jul 21, 2013 at 3:40

Matthew Wesly

1,2381 gold badge13 silver badges14 bronze badges

Collectives™ on Stack Overflow

MySQL INSERT With SELECT Statement Doing Nothing

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related