executing a select on two tables and getting the updated only [message #406907] |
Sun, 07 June 2009 09:32  |
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 #406968 is a reply to message #406962] |
Mon, 08 June 2009 00:56   |
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   |
Frank
Messages: 7901 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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #407166 is a reply to message #407112] |
Mon, 08 June 2009 21:52   |
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
|
|
|
|