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 ('[email protected]')
SELECT user_id
FROM users
WHERE email_address = '[email protected]'

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 = '[email protected]'

[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 ('[email protected]')
WHERE NOT EXISTS (SELECT user_id FROM users 
      WHERE email_address = '[email protected]')

is this even possible?

 

thank you

[wavix]

Use INNER JOIN.

[Barand]

INSERT INTO login_attempts ('identity')
SELECT email_address
FROM users
WHERE email_address = '[email protected]'

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 = '[email protected]'

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 = '[email protected]'