CODEWITHSUNDEEP
mysqlinsertnot-existsbackand
Dave
Dave

Reputation: 5416

MySQL Insert query failing

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: [email protected]
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.

Upvotes: 3

Views: 67

Answers (1)

Shadow
Shadow

Reputation: 34232

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

Upvotes: 3

Related Questions