schinni
asked on
joining two tables and getting where records doesnt exist in second
Hello Friends..
We have two tables tab1 and tab2 same structures and empid and id are keys.
We need to query for records that doesnt exist in tab2 and exist in tab1 based on keys above and then insert into t2.
And also we have a date column in tab1...
if we have two records..that match empid and id and if dates are different we have to take one record with the least date like min(Date) and insert that one record in to tab2.
any help on this would be appreciated...
Thanks,
Chinn...
We have two tables tab1 and tab2 same structures and empid and id are keys.
We need to query for records that doesnt exist in tab2 and exist in tab1 based on keys above and then insert into t2.
And also we have a date column in tab1...
if we have two records..that match empid and id and if dates are different we have to take one record with the least date like min(Date) and insert that one record in to tab2.
any help on this would be appreciated...
Thanks,
Chinn...
ASKER
No..That doesnt work..
If table 1 has two records with two different dates that doesnt exist in Tab 2 i want the record with the least date meaning min(date)
Thanks
If table 1 has two records with two different dates that doesnt exist in Tab 2 i want the record with the least date meaning min(date)
Thanks
Then you would want to first limit your result set from table 1 doing something like this:
insert into #tmptable
(select * from table1
group by employeeid,id
order by min(somedate))
Then proceed as if #tmpTable were table 1
insert into #tmptable
(select * from table1
group by employeeid,id
order by min(somedate))
Then proceed as if #tmpTable were table 1
ASKER
I am not worried about insert...i want some how to join tables and get all the records that doesnt exist in table2 if there are dups i want the one which has the least date..
Appreciate your help!
Appreciate your help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sjwales...Let me please give it a try and will let you know...shortly.
Thanks,
Thanks,
Well, I build that test sample.
Then,
tab1 has rows 1 and 2
tab2 has rows 2 (date older than row 2 in tab 1), and row 3
you want to insert in tab2 row 1 (missing in tab2) and check row 2 to update it if date is older.
Well, what you want to do is a conditional MERGE.
Hope it helps !
Kind regards,
Javier
SQL> create table tab1 (id number, coldate date);
Table created.
SQL> create table tab2 (empid number, coldate date);
Table created.
SQL> insert into tab1 values (1,sysdate);
1 row created.
SQL> insert into tab1 values (2,sysdate-1);
1 row created.
SQL> insert into tab2 values (2,sysdate-10);
1 row created.
SQL> insert into tab2 values (3,sysdate);
1 row created.
SQL> commit;
Commit complete.
Then,
tab1 has rows 1 and 2
tab2 has rows 2 (date older than row 2 in tab 1), and row 3
you want to insert in tab2 row 1 (missing in tab2) and check row 2 to update it if date is older.
SQL> select * from tab1;
ID COLDATE
---------- ---------
1 17-JAN-13
2 16-JAN-13
SQL> select * from tab2;
EMPID COLDATE
---------- ---------
2 07-JAN-13
3 17-JAN-13
Well, what you want to do is a conditional MERGE.
SQL> merge into tab2 using tab1 on (tab1.id=tab2.empid)
2 when matched then update set tab2.coldate=tab1.coldate WHERE tab2.coldate>tab1.coldate
3 when not matched then insert (tab2.empid,tab2.coldate) values (tab1.id,tab1.coldate);
1 row merged.
SQL> select * from tab2;
EMPID COLDATE
---------- ---------
2 07-JAN-13
3 17-JAN-13
1 17-JAN-13
Hope it helps !
Kind regards,
Javier
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Javier,
Thanks For the help!
I just want to do a insert if the record doesn exist.
From the below
SQL> select * from tab1;
ID COLDATE
---------- ---------
1 17-JAN-13
2 16-JAN-13
2 14-JAN-13
6 16-JAN-13
5 09-OCT-12
SQL> select * from tab2;
EMPID COLDATE
---------- ---------
3 17-JAN-13
5 09-OCT-12
I want to insert below records into tab2
2 14-JAN-13
6 16-JAN-13
1 17-JAN-13
Thanks....
Thanks For the help!
I just want to do a insert if the record doesn exist.
From the below
SQL> select * from tab1;
ID COLDATE
---------- ---------
1 17-JAN-13
2 16-JAN-13
2 14-JAN-13
6 16-JAN-13
5 09-OCT-12
SQL> select * from tab2;
EMPID COLDATE
---------- ---------
3 17-JAN-13
5 09-OCT-12
I want to insert below records into tab2
2 14-JAN-13
6 16-JAN-13
1 17-JAN-13
Thanks....
You seem to have 2 parts to your question.
First you wanted a list of everything and if there were duplicates, then report the earlier of the two dates.
There have been several examples in the previous answers.
For the insert of missing rows, doesn't this do what you need done ?
First you wanted a list of everything and if there were duplicates, then report the earlier of the two dates.
There have been several examples in the previous answers.
For the insert of missing rows, doesn't this do what you need done ?
insert into tab02
(select * from tab01 where tab01.id not in (select id from tab02));
ASKER
Sjwales,
Kind of two parts but want in one query..
find missing rows if there any get them but if there are two records(duplicate) get the one with least date.
hope i am not confusing...
Thanks,
Kind of two parts but want in one query..
find missing rows if there any get them but if there are two records(duplicate) get the one with least date.
hope i am not confusing...
Thanks,
I am sorry, but I am confused.
Of these two parts, one is "insert missing rows" - which really doesn't produce any output other than "x row(s) inserted".
The other part is a query showing all the rows, handling duplicate dates with returning the earliest.
(If a row is missing, it can't be duplicated because it's missing).
I'm afraid I can't think of a way to have one statement that does both a report on data at the same time it does an insert.
Can you elaborate any further on the requirement ?
Of these two parts, one is "insert missing rows" - which really doesn't produce any output other than "x row(s) inserted".
The other part is a query showing all the rows, handling duplicate dates with returning the earliest.
(If a row is missing, it can't be duplicated because it's missing).
I'm afraid I can't think of a way to have one statement that does both a report on data at the same time it does an insert.
Can you elaborate any further on the requirement ?
ASKER
Sorry...I have tables and data like below...
SQL> select * from tab1;
empid ID COLDATE
---------- ---------
12 1 17-JAN-13
10 2 16-JAN-13
10 2 14-JAN-13
16 6 16-JAN-13
17 5 09-OCT-12
SQL> select * from tab2;
EMPID ID COLDATE
---------- - --------
21 3 17-JAN-13
17 5 09-OCT-12
I want to write a query to the get the date as below....
10 2 14-JAN-13 --because this has least date among 2 in tab1
16 6 16-JAN-13
12 1 17-JAN-13
Thanks Again...
SQL> select * from tab1;
empid ID COLDATE
---------- ---------
12 1 17-JAN-13
10 2 16-JAN-13
10 2 14-JAN-13
16 6 16-JAN-13
17 5 09-OCT-12
SQL> select * from tab2;
EMPID ID COLDATE
---------- - --------
21 3 17-JAN-13
17 5 09-OCT-12
I want to write a query to the get the date as below....
10 2 14-JAN-13 --because this has least date among 2 in tab1
16 6 16-JAN-13
12 1 17-JAN-13
Thanks Again...
OK try this (but it still doesn't address the insert part):
select a.id, min(case when (a.last < b.last and b.last is not null) then a.last else b.last end)
from tab01 a
left outer join tab02 b
on a.id = b.id
group by a.id
order by a.id
You can use a query instead of tab1 in MERGE statement.
Sorry, I'm using phone :/
Sorry, I'm using phone :/
Well, I'm back to laptop.
Something like this?
So, row 1 does not exists in tab2, and it's duplicated. You want the minor date to be inserted.
Something like this?
SQL> insert into tab1 values (1,sysdate-5);
1 fila creada.
SQL> select * from tab1;
ID COLDATE
---------- --------
1 17/01/13
2 16/01/13
1 12/01/13
SQL> select * from tab2;
EMPID COLDATE
---------- --------
2 07/01/13
3 17/01/13
So, row 1 does not exists in tab2, and it's duplicated. You want the minor date to be inserted.
SQL> merge into tab2
2 using (select id, min(coldate) coldate from tab1 group by id) tab1
3 on (tab1.id=tab2.empid)
4 when matched then update set tab2.coldate=tab1.coldate WHERE tab2.coldate>tab1.coldate
5 when not matched then insert (tab2.empid,tab2.coldate) values (tab1.id,tab1.coldate);
1 fila fusionada.
SQL> select * from tab2;
EMPID COLDATE
---------- --------
2 07/01/13
3 17/01/13
1 12/01/13
http://stackoverflow.com/questions/2192560/insert-record-into-table-if-entry-does-not-exist-in-another-table-with-an-extra
More thoughts here:
http://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice