Home » SQL & PL/SQL » SQL & PL/SQL » executing a select on two tables and getting the updated only
executing a select on two tables and getting the updated only [message #406907] Sun, 07 June 2009 09:32 Go to next message
dyahav
Messages: 32
Registered: March 2009
Member
Hi all,

I have two identical tables called first and second. Each of them has the following columns:
id primary key,
name varchar,
lastUpdateTime Date.

The first table contains fresh rows from the current day and the second contains all rows from
the life cycle of the application.

I want to execute a query to fetch rows from the second table which contains all the rows,
but if there are newer rows in the first table, I want to get them instead. Also, new rows that appear
in the first table and not in the second should be fetched.

Anyone has an idea how to solve it?
Thanks a lot!
dyahav
Re: executing a select on two tables and getting the updated only [message #406908 is a reply to message #406907] Sun, 07 June 2009 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
use MINUS
Re: executing a select on two tables and getting the updated only [message #406909 is a reply to message #406907] Sun, 07 June 2009 09:38 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
SELECT *
FROM first f1
UNION ALL
SELECT *
FROM second s 
WHERE NOT EXISTS(SELECT 1
                 FROM first f2
                 WHERE s.id = f2.id);
By
Vamsi
Re: executing a select on two tables and getting the updated only [message #406955 is a reply to message #406907] Mon, 08 June 2009 00:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Outer join with nvl.

Now you have three solutions. Up to you to test them all to see which performs best in your situation.
Please report back on any findings.
Re: executing a select on two tables and getting the updated only [message #406960 is a reply to message #406955] Mon, 08 June 2009 00:25 Go to previous messageGo to next message
dyahav
Messages: 32
Registered: March 2009
Member
Hi
Could you please give an example?
Thanks
dyahav
Re: executing a select on two tables and getting the updated only [message #406962 is a reply to message #406907] Mon, 08 June 2009 00:28 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: executing a select on two tables and getting the updated only [message #406968 is a reply to message #406962] Mon, 08 June 2009 00:56 Go to previous messageGo to next message
dyahav
Messages: 32
Registered: March 2009
Member
DROP TABLE ALL_ROWS;
DROP TABLE UPDATED_ROWS;
CREATE TABLE ALL_ROWS
(
ID NUMBER primary key,
NAME VARCHAR2(32 BYTE),
Last_update_time Date
);

CREATE TABLE UPDATED_ROWS
(
ID NUMBER primary key,
NAME VARCHAR2(32 BYTE),
Last_update_time Date
);

BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO ALL_ROWS VALUES (i, 'from_all_rows', sysdate);
END LOOP;
END;

BEGIN
FOR i IN 500..700 LOOP
INSERT INTO UPDATED_ROWS VALUES (i, 'from_updated_rows', sysdate);
END LOOP;
END;

The desired result should be a union between the tables but a priority is given to row from the updated_rows table if exists.
Thanks
dyahav
Re: executing a select on two tables and getting the updated only [message #406969 is a reply to message #406960] Mon, 08 June 2009 01:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
dyahav wrote on Mon, 08 June 2009 07:25
Hi
Could you please give an example?
Thanks
dyahav

How about you trying to do it yourself? I will provide you the steps:
1) How would you outer join these two tables in such a way that all the records from the big table are selected, regardless if there is a record in the small table?

2) From step 1, you have a resultset containing columns like big_table.id and small_table.id.
small_table.id will be null if there was no corresponding record found. In that case you want big_table.id
Now, how could you accomplish that using nvl?
Re: executing a select on two tables and getting the updated only [message #407112 is a reply to message #406968] Mon, 08 June 2009 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here are a couple of examples (as a reference):
SQL> CREATE TABLE ALL_ROWS
  2  (
  3   ID NUMBER primary key,
  4   NAME VARCHAR2(32 BYTE),
  5   Last_update_time Date
  6  )
  7  /

Table created.

SQL> CREATE TABLE UPDATED_ROWS
  2  (
  3   ID NUMBER primary key,
  4   NAME VARCHAR2(32 BYTE),
  5   Last_update_time Date
  6  )
  7  /

Table created.

SQL> BEGIN
  2    FOR i IN 1..10 LOOP
  3     INSERT INTO ALL_ROWS VALUES (i, 'from_all_rows', sysdate);
  4    END LOOP;
  5    dbms_lock.sleep(5);
  6    FOR i IN 5..7 LOOP
  7      INSERT INTO UPDATED_ROWS VALUES (i, 'from_updated_rows', sysdate);
  8    END LOOP;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from ALL_ROWS;
        ID NAME                             LAST_UPDATE_TIME
---------- -------------------------------- -------------------
         1 from_all_rows                    08/06/2009 15:43:36
         2 from_all_rows                    08/06/2009 15:43:36
         3 from_all_rows                    08/06/2009 15:43:36
         4 from_all_rows                    08/06/2009 15:43:36
         5 from_all_rows                    08/06/2009 15:43:36
         6 from_all_rows                    08/06/2009 15:43:36
         7 from_all_rows                    08/06/2009 15:43:36
         8 from_all_rows                    08/06/2009 15:43:36
         9 from_all_rows                    08/06/2009 15:43:36
        10 from_all_rows                    08/06/2009 15:43:36

10 rows selected.

SQL> select * from UPDATED_ROWS;
        ID NAME                             LAST_UPDATE_TIME
---------- -------------------------------- -------------------
         5 from_updated_rows                08/06/2009 15:43:41
         6 from_updated_rows                08/06/2009 15:43:41
         7 from_updated_rows                08/06/2009 15:43:41

3 rows selected.

SQL> -- MINUS
SQL> select * from UPDATED_ROWS 
  2  union all
  3  select * from ALL_ROWS
  4  where id in ( select id from ALL_ROWS
  5                minus 
  6                select id from UPDATED_ROWS )
  7  order by 1
  8  /
        ID NAME                             LAST_UPDATE_TIME
---------- -------------------------------- -------------------
         1 from_all_rows                    08/06/2009 15:43:36
         2 from_all_rows                    08/06/2009 15:43:36
         3 from_all_rows                    08/06/2009 15:43:36
         4 from_all_rows                    08/06/2009 15:43:36
         5 from_updated_rows                08/06/2009 15:43:41
         6 from_updated_rows                08/06/2009 15:43:41
         7 from_updated_rows                08/06/2009 15:43:41
         8 from_all_rows                    08/06/2009 15:43:36
         9 from_all_rows                    08/06/2009 15:43:36
        10 from_all_rows                    08/06/2009 15:43:36

10 rows selected.

SQL> -- NOT EXISTS
SQL> select * from UPDATED_ROWS 
  2  union all
  3  select * from ALL_ROWS a
  4  where not exists ( select null from UPDATED_ROWS u
  5                     where u.id = a.id )
  6  order by 1
  7  /
        ID NAME                             LAST_UPDATE_TIME
---------- -------------------------------- -------------------
         1 from_all_rows                    08/06/2009 15:43:36
         2 from_all_rows                    08/06/2009 15:43:36
         3 from_all_rows                    08/06/2009 15:43:36
         4 from_all_rows                    08/06/2009 15:43:36
         5 from_updated_rows                08/06/2009 15:43:41
         6 from_updated_rows                08/06/2009 15:43:41
         7 from_updated_rows                08/06/2009 15:43:41
         8 from_all_rows                    08/06/2009 15:43:36
         9 from_all_rows                    08/06/2009 15:43:36
        10 from_all_rows                    08/06/2009 15:43:36

10 rows selected.

SQL> -- NOT IN
SQL> select * from UPDATED_ROWS 
  2  union all
  3  select * from ALL_ROWS
  4  where id not in ( select id from UPDATED_ROWS )
  5  order by 1
  6  /
        ID NAME                             LAST_UPDATE_TIME
---------- -------------------------------- -------------------
         1 from_all_rows                    08/06/2009 15:43:36
         2 from_all_rows                    08/06/2009 15:43:36
         3 from_all_rows                    08/06/2009 15:43:36
         4 from_all_rows                    08/06/2009 15:43:36
         5 from_updated_rows                08/06/2009 15:43:41
         6 from_updated_rows                08/06/2009 15:43:41
         7 from_updated_rows                08/06/2009 15:43:41
         8 from_all_rows                    08/06/2009 15:43:36
         9 from_all_rows                    08/06/2009 15:43:36
        10 from_all_rows                    08/06/2009 15:43:36

10 rows selected.

SQL> -- OUTER JOIN + NVL
SQL> select a.id, nvl(u.name, a.name) name, 
  2         nvl(u.last_update_time, a.last_update_time) last_update_time
  3  from UPDATED_ROWS u, ALL_ROWS a
  4  where u.id (+) = a.id
  5  order by 1
  6  /
        ID NAME                             LAST_UPDATE_TIME
---------- -------------------------------- -------------------
         1 from_all_rows                    08/06/2009 15:43:36
         2 from_all_rows                    08/06/2009 15:43:36
         3 from_all_rows                    08/06/2009 15:43:36
         4 from_all_rows                    08/06/2009 15:43:36
         5 from_updated_rows                08/06/2009 15:43:41
         6 from_updated_rows                08/06/2009 15:43:41
         7 from_updated_rows                08/06/2009 15:43:41
         8 from_all_rows                    08/06/2009 15:43:36
         9 from_all_rows                    08/06/2009 15:43:36
        10 from_all_rows                    08/06/2009 15:43:36

10 rows selected.

SQL> -- ANALYTIC
SQL> with 
  2    data as (
  3      select 1 rn, id, name, last_update_time from UPDATED_ROWS
  4      union all
  5      select 2 rn, id, name, last_update_time from ALL_ROWS
  6    ),
  7    numbered as (
  8      select id, name, last_update_time,
  9             row_number() over (partition by id order by rn) rn
 10      from data
 11    )
 12  select id, name, last_update_time
 13  from numbered
 14  where rn = 1
 15  order by 1
 16  /
        ID NAME                             LAST_UPDATE_TIME
---------- -------------------------------- -------------------
         1 from_all_rows                    08/06/2009 15:43:36
         2 from_all_rows                    08/06/2009 15:43:36
         3 from_all_rows                    08/06/2009 15:43:36
         4 from_all_rows                    08/06/2009 15:43:36
         5 from_updated_rows                08/06/2009 15:43:41
         6 from_updated_rows                08/06/2009 15:43:41
         7 from_updated_rows                08/06/2009 15:43:41
         8 from_all_rows                    08/06/2009 15:43:36
         9 from_all_rows                    08/06/2009 15:43:36
        10 from_all_rows                    08/06/2009 15:43:36

10 rows selected.

SQL> -- AGGREGATE
SQL> with 
  2    data as (
  3      select 1 rn, id, name, last_update_time from UPDATED_ROWS
  4      union all
  5      select 2 rn, id, name, last_update_time from ALL_ROWS
  6    ),
  7    minim as ( select id, min(rn) rn from data group by id )
  8  select id, name, last_update_time 
  9  from data
 10  where (id, rn) in (select id, rn from minim)
 11  order by 1
 12  /
        ID NAME                             LAST_UPDATE_TIME
---------- -------------------------------- -------------------
         1 from_all_rows                    08/06/2009 15:43:36
         2 from_all_rows                    08/06/2009 15:43:36
         3 from_all_rows                    08/06/2009 15:43:36
         4 from_all_rows                    08/06/2009 15:43:36
         5 from_updated_rows                08/06/2009 15:43:41
         6 from_updated_rows                08/06/2009 15:43:41
         7 from_updated_rows                08/06/2009 15:43:41
         8 from_all_rows                    08/06/2009 15:43:36
         9 from_all_rows                    08/06/2009 15:43:36
        10 from_all_rows                    08/06/2009 15:43:36

10 rows selected.

SQL> with 
  2    data as (
  3      select 1 rn, id, name, last_update_time from UPDATED_ROWS
  4      union all
  5      select 2 rn, id, name, last_update_time from ALL_ROWS
  6    )
  7  select id, name, last_update_time 
  8  from data d1
  9  where rn = ( select min(rn) from data d2 where d2.id = d1.id )
 10  order by 1
 11  /
        ID NAME                             LAST_UPDATE_TIME
---------- -------------------------------- -------------------
         1 from_all_rows                    08/06/2009 15:43:36
         2 from_all_rows                    08/06/2009 15:43:36
         3 from_all_rows                    08/06/2009 15:43:36
         4 from_all_rows                    08/06/2009 15:43:36
         5 from_updated_rows                08/06/2009 15:43:41
         6 from_updated_rows                08/06/2009 15:43:41
         7 from_updated_rows                08/06/2009 15:43:41
         8 from_all_rows                    08/06/2009 15:43:36
         9 from_all_rows                    08/06/2009 15:43:36
        10 from_all_rows                    08/06/2009 15:43:36

10 rows selected.

Regards
Michel
Re: executing a select on two tables and getting the updated only [message #407156 is a reply to message #407112] Mon, 08 June 2009 14:05 Go to previous messageGo to next message
dyahav
Messages: 32
Registered: March 2009
Member
Thanks a lot for the detailed answer!
Re: executing a select on two tables and getting the updated only [message #407162 is a reply to message #407112] Mon, 08 June 2009 19:59 Go to previous messageGo to next message
alamtapash@gmail.com
Messages: 8
Registered: June 2009
Location: DHAKA
Junior Member
Hi Michel,

thanks a lot for your step by step example. it helps many of us to learn quickly.

regards,

Shariful
Re: executing a select on two tables and getting the updated only [message #407166 is a reply to message #407112] Mon, 08 June 2009 21:52 Go to previous messageGo to next message
alamtapash@gmail.com
Messages: 8
Registered: June 2009
Location: DHAKA
Junior Member
Hi Michel,

I have followed all the process you mentioned and all went through except the following one:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> BEGIN
2 FOR i IN 1..10 LOOP
3 INSERT INTO ALL_ROWS VALUES (i, 'from_all_rows', sysdate);
4 END LOOP;
5 dbms_lock.sleep(5);
6 FOR i IN 5..7 LOOP
7 INSERT INTO UPDATED_ROWS VALUES (i, 'from_updated_rows', sysdate);
8 END LOOP;
9 END;
10 /
dbms_lock.sleep(5);
*
ERROR at line 5:
ORA-06550: line 5, column 7:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 5, column 7:
PL/SQL: Statement ignored

It was seen that the above block was through with you. would you pls. tell me what is the problem with my db? should i declare DBMS_LOCK prior to running the above block?

Thanks
Re: executing a select on two tables and getting the updated only [message #407167 is a reply to message #406907] Mon, 08 June 2009 22:20 Go to previous message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
sys.dbms_lock.sleep(5);

& your schema must be granted execute on function above
Previous Topic: Juliandate Query + last full week
Next Topic: pls-00103 (merged)
Goto Forum:
  


Current Time: Wed Dec 07 06:52:06 CST 2016

Total time taken to generate the page: 0.11021 seconds