insert if user exists

[Destramic]

hey guys im wondering if it's possible to insert a row if a value exists inside another table?

 

for instance the code below i wish to make a log in attempt record if the user actually exists in the users table

INSERT INTO login_attempts ('identity')
VALUES ('name@example.com')
SELECT user_id
FROM users
WHERE email_address = 'name@example.com'

thanks you

[Barand]

You are mixing statement syntaxes here.

 

What do want to insert into the identity column? If it is the id of the user then

INSERT INTO login_attempts ('identity')
SELECT user_id
FROM users
WHERE email_address = 'name@example.com'

Edited May 15, 2015 by Barand

[Destramic]

well id like to check if users exists first before entering a log in attempt...after a bit more researching i think i need something like this but it doesn't work

INSERT INTO login_attempts (identity)
VALUES ('destramic@hotmail.com')
WHERE NOT EXISTS (SELECT user_id FROM users 
      WHERE email_address = 'destramic@hotmail.com')

is this even possible?

 

thank you

[wavix]

Use INNER JOIN.

[Barand]

INSERT INTO login_attempts ('identity')
SELECT email_address
FROM users
WHERE email_address = 'name@example.com'

If email doesn't exist in users table then no insert.

[Destramic]

INSERT INTO login_attempts ('identity')
SELECT email_address
FROM users
WHERE email_address = 'name@example.com'

If email doesn't exist in users table then no insert.

 

 

brilliant thank you...is it also possible to add custom values to this query other than what is selected from the users table?

[Barand]

Sure, you can select constant values just as in any select statement

INSERT INTO login_attempts ('identity', colA, colB, colC)
SELECT email_address, 123, 'ABC', NOW()
FROM users
WHERE email_address = 'name@example.com'