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

Question

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?

What do you mean by *add a list_id to them*? Hardcoded value or? — OzrenTkalcecKrznaric, Jun 29 '13 at 00:27
Yes, I have a hard coded value, that I need to put in... So far it seems like none of the answers are working... I'm getting an error: Unknown column 'list_id' in 'field list' The Contacts table doesn't have a list_id in it... — Bill, Jun 29 '13 at 00:39
my answer should work if server is of version 2005 or higher; which version is it? EDIT: whooa, it might not be SQL server at all... which RDBMS? — OzrenTkalcecKrznaric, Jun 29 '13 at 00:42

score 3 · Accepted Answer · edited Jun 30 '13 at 00:58

3

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
)

edited Jun 30 '13 at 00:58

Danny Beckett

20,529
24
107
134

answered Jun 29 '13 at 00:24

OzrenTkalcecKrznaric

5,535
4
34
57

I keep getting this error: Unknown column 'list_id' in 'field list' – Bill Jun 29 '13 at 00:41
Could you try with second line: `SELECT contact_id, 1234`? – OzrenTkalcecKrznaric Jun 29 '13 at 00:44

Fabian Bigler · Answer 2 · 2013-06-29T00:45:53.123

2

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

edited Jun 29 '13 at 00:45

answered Jun 29 '13 at 00:34

Fabian Bigler

10,403
6
47
70

Thank you for repeating my answer. – OzrenTkalcecKrznaric Jun 29 '13 at 00:39
2

@OzrenTkalčecKrznarić It's not your answer. I was busy setting this up in SQL Fiddle. This was pure coincidence... – Fabian Bigler Jun 29 '13 at 00:44
Hehe, interesting how the SQL language works same for different people :) – OzrenTkalcecKrznaric Jun 29 '13 at 00:47

score 2 · Answer 3 · edited May 23 '17 at 10:32

2

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

edited May 23 '17 at 10:32

Community

1
1

answered Jun 29 '13 at 02:58

Surendra

711
5
15

Wow, I didn't know that. Interesting stuff, but unfortunately syntactically not so obvious as NOT IN. However, I would surely use NOT EXISTS for large number of records. +1 – OzrenTkalcecKrznaric Jun 30 '13 at 08:28

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

3 Answers3