MySQL Insert query failing

Question

I'm using backand.com to make a query that attempts to create a record in a users table if the email isn't already used:

INSERT INTO users (email, firstName, lastName, password)
SELECT * FROM (SELECT '{{email}}', 'test', 'person', '{{password}}') AS tmp
WHERE NOT EXISTS (
    SELECT email FROM users WHERE email = '{{email}}'
) LIMIT 1;

It validates, but when I run it with params:

email: dave@gmail.com
password: test

I get the error:

An error occurred, please try again or contact the administrator. Error details: Duplicate column name 'test'

Why is this failing? I don't get it.

Thanks.

please change '' to `` and try. – Azeez Kallayi Mar 07 '16 at 14:03 — Azeez Kallayi, Mar 07 '16 at 14:03

score 3 · Accepted Answer · answered Mar 07 '16 at 14:12

3

The problem is that you use 'test' as password because you have 'test' as firstName already. Since you are not providing column aliases, the field name for constant values bacome the constant itself. Solution: provide explicit aliases for all 4 fields in the subselect:

SELECT '{{email}}' as email, 'test' as firstName, 'person' as lastName, '{{password}}' as pwd

answered Mar 07 '16 at 14:12

Shadow

33,525
10
51
64

Thanks, this corrected the issue. How would I modify the query to return the new id if successful? – Dave Mar 07 '16 at 14:18
This is a different question, so pls ask it in its own post, not just as a follow-up comment. But before asking it, I would search SO for an answer. I'm quite sure that this question has already been asked. – Shadow Mar 07 '16 at 14:24
No worries, I added SELECT ROW_COUNT() > 0 – Dave Mar 07 '16 at 14:24

MySQL Insert query failing

1 Answers1