SQL INSERT with an if statement

Question

I have a table called notification, with two columns:

provider
email

The values look like this:

For all providers that do not have the email [email protected], I want to insert another row with the provider and the email [email protected]. For example, in the above query, the result of the INSERT statement would be: INSERT INTO notification VALUES ('apple', '[email protected]').

I have a table with thousands of rows like this, how would I write such an INSERT statement?

use t-sql - transact sql has conditional expressions to execute different statements based on logical expressions — Paul Sullivan
– Paul Sullivan, Commented Mar 26, 2012 at 21:45
Given your test data, wouldn't you also want to insert a row for abc? — Tim Lehner
– Tim Lehner, Commented Mar 26, 2012 at 22:00
The provider abc also lacks the email [email protected] (but does have [email protected]). — onedaywhen
– onedaywhen, Commented Mar 27, 2012 at 7:40

hkf · Accepted Answer · 2012-03-26 22:29:07Z

2

INSERT INTO notification
SELECT DISTINCT provider, '[email protected]'
FROM notification
WHERE provider NOT IN
(SELECT DISTINCT provider
FROM notification
WHERE email = '[email protected]')

edited Mar 26, 2012 at 22:29

answered Mar 26, 2012 at 21:57

hkf

4,5201 gold badge32 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hkf Over a year ago

distinct on the inner query is there, and it's better performance wise too (slightly) (compared to having distinct on the outer select)

Tim Lehner Over a year ago

This can insert duplicate rows (in this case for "abc").

Tim Lehner · Accepted Answer · 2012-03-26 21:59:03Z

0

This should get you what you're after:

insert into notification (provider, email)
select distinct provider, '[email protected]'
from notification
where provider not in (
        select provider
        from notification
        where email like '[email protected]'
    )

edited Mar 26, 2012 at 21:59

answered Mar 26, 2012 at 21:53

Tim Lehner

15.3k4 gold badges61 silver badges78 bronze badges

2 Comments

Tony Hopkinson Over a year ago

Nope. That would insert a new record for every record that wasn't like [email protected], as opposed to one for each provider that doesn't have a [email protected].

Tim Lehner Over a year ago

I deleted that nearly as fast as I posted it.

Collectives™ on Stack Overflow

SQL INSERT with an if statement

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related