Finding field value pairs using SQL

Question

I have difficulties summarizing the issue into a nice, neat title, so the title may be misleading. Here is the situation

Table 1 has an ID, an Issue, and a resolution date, so it is expected that some dates will be null. ID's can be assigned to multiple issues as well.

Table_1:

ID | Issue | Date
1  | a     | 1/1
2  | a     | 1/1
3  | b     | 
4  | c     | 1/2

I use another table to update this table, so for this example, the data in table_2 looks like:

ID | Issue | Date
3  | b     | 1/3
1  | b     | 1/3

Now, I have one query which will update table_1 dates using table_2 information, based on the ID/Issue pairing, using something like this:

Update Table_1 tab1
left outer join Table_2 tab2
on tab1.id = tab2.id and tab1.issue = tab2.issue
set tab1.date = tab2.date

However, there will be times where table 2 has ID/Issue pairs that are not in table_1. I would like to instead insert those rows into table_1, but I'm not sure how to do this.

If it was just one single field, say ID, i could just do something like:

insert into table_1 (ID, Issue, Date)
select ID, Issue, Date
from table_2 where table_2.ID not in (select ID from table_1)

How would I do this for an ID/Issue pairing? Using the above example, I would want to insert the following row from table_2 into table_1:

1 | b | 1/3

since an ID/Issue pair of 1/b exists in table 2 but not table 1.

How would I go about selecting from table 2 the id/issue pairs that do not exist in table 1?

Some databases support the concept of `MERGE` ([msft](https://msdn.microsoft.com/en-us/library/bb510625.aspx))([Oracle](https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm))([MySQL on duplicate key update](http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update/548570#548570)) which will either update if key value exists, or insert. if no key value found. — xQbert, Oct 23 '15 at 18:05

score 1 · Answer 1 · answered Oct 23 '15 at 18:07

1

You can use a left outer join to do this:

insert into table_1 (ID, Issue, Date)
select ID, Issue, Date
  from table_2 t2
  left outer join table_1 t1 on t1.ID = t2.ID and
                                t1.Issue = t2.Issue
 where t1.ID is null

This guarantees you to get every row in table_2 and then limit the rows in table two if there isn't a match in table_1.

answered Oct 23 '15 at 18:07

danjuggler

1,261
2
21
38

Thank you Daniel, I never thought of doing this before. Much appreciated. – user3654225 Oct 23 '15 at 18:28

score 0 · Answer 2 · answered Oct 23 '15 at 18:12

One method is to use not exists:

insert into table_1 (ID, Issue, Date)
    select ID, Issue, Date
    from table_2
    where not exists (select 1
                      from table_1
                      where table_2.ID = table_1.ID and table_2.issue = table_1.issue
                     );

Finding field value pairs using SQL

2 Answers2