joining two tables

[yxez]

Hi dear friends,

Sorry to keep posting similar problems.

I have two tables again ENTRY, EXIT

ENTRY (name,entry_date,days_stayed)

name entry_date days_stayed
==== ======== ==========
paris hilton 01-JAN-07
paris hilton 10-JAN-07
paris hilton 20-FEB-07
paris hilton 31-JUL-07


EXIT (name,exit_date)

name entry_date
==== ========
paris hilton 08-JAN-07
paris hilton 18-JAN-07
paris hilton 25-FEB-07


I want to update the days_stayed column in ENTRY table with
match EXIT table (assuming the sorted entry/exit dates match correspondingly)

Thanks again a million times for your unfailing support, you all surely
helped lightened/eased burdens of lots of depressed people.


Thanks a lot

[ebrian]

SQL> select * from entry;

NAME                 ENTRY_DAT DAYS_STAYED
-------------------- --------- ----------------------------------------
paris hilton         01-JAN-07
paris hilton         10-JAN-07
paris hilton         20-JAN-07
paris hilton         31-JAN-07

SQL> select * from depature;

NAME                 EXIT_DATE
-------------------- ---------
paris hilton         08-JAN-07
paris hilton         18-JAN-07
paris hilton         25-JAN-07

SQL> update entry e1
  2  set days_stayed = nvl(to_char((select (b.exit_date - a.entry_date ) from
  3     (select name
  4            ,entry_date
  5            ,row_number() over (partition by name order by entry_date) rn1
  6     from entry) a,
  7     (select name
  8            ,exit_date
  9            ,row_number() over (partition by name order by exit_date) rn2
 10     from depature) b
 11  where a.rn1 = b.rn2
 12  and a.name = b.name
 13  and e1.entry_date = a.entry_date)), 'the spoiled drunk is still here');

4 rows updated.

SQL> select * from entry;

NAME                 ENTRY_DAT DAYS_STAYED
-------------------- --------- ----------------------------------------
paris hilton         01-JAN-07 7
paris hilton         10-JAN-07 8
paris hilton         20-JAN-07 5
paris hilton         31-JAN-07 the spoiled drunk is still here

.

If you want to avoid the comment for days_stayed, you could just use the following:

update entry e1
set days_stayed = (select (b.exit_date - a.entry_date ) from 
	(select name
	       ,entry_date
	       ,row_number() over (partition by name order by entry_date) rn1
	from entry) a,
	(select name
	       ,exit_date
	       ,row_number() over (partition by name order by exit_date) rn2
	from depature) b 
where a.rn1 = b.rn2
and a.name = b.name
and e1.entry_date = a.entry_date);