CODEWITHSUNDEEP
sqlt-sql
Bill
Bill

Reputation: 5668

Add rows from a table where they're not in the other table

I have the following tables:

Contacts
      contact_id, contact_name, etc.
assigned_lists
      contact_id, list_id

Each contact can be associated with more than 1 list, which is stored in the assigned_lists column.

I have a lot of contacts who are not associated with any lists. How would I insert all the contact_id's that are not associated with a list into assigned_lists?

Upvotes: 0

Views: 68

Answers (3)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

Try with:

INSERT INTO assigned_lists (contact_id, list_id)
SELECT contact_id, 24
FROM Contacts
WHERE contact_id NOT IN (
  SELECT DISTINCT contact_id
  FROM assigned_lists
)

Upvotes: 3

Surendra
Surendra

Reputation: 721

you can try the below 

INSERT INTO assigned_lists (contact_id, list_id)
SELECT contact_id, list_id = 1234
FROM Contacts C
WHERE NOT EXISTS (SELECT 1 FROM assigned_lists A WHERE A.contact_id  = C.contact_id )

remember, not exists clause has a performance edge over not in clause.

A detailed explanation about the same can be found here NOT IN vs NOT EXISTS

Upvotes: 2

Fabian Bigler
Fabian Bigler

Reputation: 10895

INSERT INTO assigned_lists (contact_id, list_id)
SELECT contact_id, @yourNewListID
FROM Contacts
WHERE contact_id NOT IN (SELECT contact_id FROM assigned_lists)

SQL Fiddle Example: http://sqlfiddle.com/#!3/d59d1e/1

Upvotes: 2

Related Questions