SQL to handle potential for null result from subquery during insert statement-CodePudding

I am performing an insert, and using a subquery to select an email address for the insert statement, as such:

INSERT INTO users (id, email, date) 
VALUES (123,
        (SELECT email from list_of_emails 
         WHERE email = '[email protected]'),
        NOW())

The users table has a non-null constraint on an email address, so what I am trying to catch is the potential that there may not be an email address that matches the subquery, in which case a not-null constraint violation occurs.

Any suggestion on how to catch this value error during the subquery and return some error message like "email address was not found"

CodePudding user response：

Just use INSERT ... SELECT ...; form:

INSERT INTO users (id, email, date)
SELECT DISTINCT 123, email, now()
  FROM email
 WHERE email = '[email protected]'
;

This avoids attempting to insert when no email rows are found. Hopefully, you don't have duplicate email entries, as that would have been a problem in the original as well. I added a DISTINCT just in case, since I don't know your schema. Remove the DISTINCT if email is guaranteed unique in the email table.

CodePudding user response：

You can use the WHERE EXISTS.

INSERT INTO users (id, email, date) VALUES (
123,
(
  SELECT email from list_of_emails WHERE email = '[email protected]'
),
now() 
)
WHERE EXISTS (SELECT email from list_of_emails WHERE email = '[email protected]')