Home » SQL & PL/SQL » SQL & PL/SQL » Need Update Query
Need Update Query [message #184883] Fri, 28 July 2006 08:40 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
i have two tables they are tab1 and tab2
both the table having 4 columns say a, b, c, d

my sample records are
tab1
a b c d
1 1 1 2
1 1 1 1
1 1 1 3
2 2 2 1
3 3 3 2
4 4 4 1
4 4 4 3
5 5 5 6

tab2
a b c d
1 1 1 null
1 1 1 null
1 1 1 null
2 2 2 null
3 3 3 null
4 4 4 null
4 4 4 null
5 5 5 null
5 5 5 null

tab2 is almost same as tab1 except column d because we have created tab2 based on tab1.

i have to update tab2(d) from tab2(b) by mapping the column a,b,c which is present only once in both the table

the result after update should be
tab2
a b c d
1 1 1 null
1 1 1 null
1 1 1 null
2 2 2 1
3 3 3 2
4 4 4 null
4 4 4 null
5 5 5 null
5 5 5 null

because the remainings sets are having more than one row for that columns(a,b,c) combinations either in tab1 or tab2

Re: Need Update Query [message #184884 is a reply to message #184883] Fri, 28 July 2006 08:44 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
here tab1 and tab2 are sample tables. in real both tables have 5 million plus records.

i have written the update query and it is doing exactly what it supposed to do. but it is taking considerably long time.

i'm looking for some effective solutions in terms of performance.
Re: Need Update Query [message #184887 is a reply to message #184883] Fri, 28 July 2006 09:00 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

If that is the case...better to post ur update query...so that anyone can optimise it...

Naveen
Re: Need Update Query [message #184890 is a reply to message #184884] Fri, 28 July 2006 09:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You say
Quote:

have to update tab2(d) from tab2(b) by mapping the column a,b,c which is present only once in both the table


But the results you want show you updating tab2.d from tab1.b for those rows.

Which is it?
Re: Need Update Query [message #184894 is a reply to message #184890] Fri, 28 July 2006 09:14 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Sorry i have to update tab2(d) from tab2(d) by mapping the column a,b,c which is present only once in both the table.

that should be d not b.

Re: Need Update Query [message #184895 is a reply to message #184894] Fri, 28 July 2006 09:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Would you mind previewing your posts before submitting them.
Youv'e got it wrong again.

Quote:

Sorry i have to update tab2(d) from tab2(d)
Re: Need Update Query [message #184901 is a reply to message #184895] Fri, 28 July 2006 09:29 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
oops.
i have to update tab2(d) from tab1(d) by mapping the column a,b,c which is present only once in both the table

to be more simpler i have to update the value of d in tab2 from d of tab1 those record is present only once in both table.
Re: Need Update Query [message #184902 is a reply to message #184895] Fri, 28 July 2006 09:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that you want to update tab2.d from tab1.d,

This wil do it:
SQL> create table temp_tab1 (a number,b number,c number,d number);

Table created.

SQL> 
SQL> create table temp_tab2 (a number,b number,c number,d number);

Table created.

SQL> insert into temp_tab1 values(1,1,1,2);
SQL> insert into temp_tab1 values(1,1,1,1);
SQL> insert into temp_tab1 values(1,1,1,3);
SQL> insert into temp_tab1 values(2,2,2,1);
SQL> insert into temp_tab1 values(3,3,3,2);
SQL> insert into temp_tab1 values(4,4,4,1);
SQL> insert into temp_tab1 values(4,4,4,3);
SQL> insert into temp_tab1 values(5,5,5,6);
SQL> 
SQL> insert into temp_tab2 values(1,1,1,null);
SQL> insert into temp_tab2 values(1,1,1,null);
SQL> insert into temp_tab2 values(1,1,1,null);
SQL> insert into temp_tab2 values(2,2,2,null);
SQL> insert into temp_tab2 values(3,3,3,null);
SQL> insert into temp_tab2 values(4,4,4,null);
SQL> insert into temp_tab2 values(4,4,4,null);
SQL> insert into temp_tab2 values(5,5,5,null);
SQL> insert into temp_tab2 values(5,5,5,null);
SQL> 
SQL> 
SQL> update temp_tab2 tb2
  2  set    d = (select t1.d
  3              from   temp_tab1 t1      
  4                    ,(select a,b,c from temp_tab1 having count(*) = 1 group by a,b,c
  5                     intersect
  6                      select a,b,c from temp_tab2 having count(*) = 1 group by a,b,c) t2
  7              where  t2.a = t1.a
  8              and    t2.b = t1.b
  9              and    t2.c = t1.c
 10              and    t2.a = tb2.a
 11              and    t2.b = tb2.b
 12              and    t2.c = tb2.c);

9 rows updated.

SQL> select * from temp_tab2;

         A          B          C          D
---------- ---------- ---------- ----------
         1          1          1
         1          1          1
         1          1          1
         2          2          2          1
         3          3          3          2
         4          4          4
         4          4          4
         5          5          5
         5          5          5


Its quite possible that this second method will be quicker. I know it looks like SQL Server, but give it a chance - there's a lot less io going on

SQL> create global temporary table temp_gtt on commit preserve rows as
  2  select t2.a
  3        ,t2.b
  4        ,t2.c
  5        ,t1.d
  6  from   temp_tab1 t1      
  7        ,(select a,b,c from temp_tab1 having count(*) = 1 group by a,b,c
  8         intersect
  9          select a,b,c from temp_tab2 having count(*) = 1 group by a,b,c) t2
 10  where  t2.a = t1.a
 11  and    t2.b = t1.b
 12  and    t2.c = t1.c;

Table created.

SQL> 
SQL> select * from temp_gtt;

         A          B          C          D
---------- ---------- ---------- ----------
         2          2          2          1
         3          3          3          2

SQL> 
SQL> update temp_tab2 tb2
  2  set    d = (select t1.d
  3              from   temp_gtt t1
  4              where  t1.a = tb2.a
  5              and    t1.b = tb2.b
  6              and    t1.c = tb2.c);

9 rows updated.

SQL>             
SQL> select * from temp_tab2;            

         A          B          C          D
---------- ---------- ---------- ----------
         1          1          1
         1          1          1
         1          1          1
         2          2          2          1
         3          3          3          2
         4          4          4
         4          4          4
         5          5          5
         5          5          5
Re: Need Update Query [message #184903 is a reply to message #184902] Fri, 28 July 2006 09:44 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
thanks JRowbottom.
Previous Topic: Update With Join
Next Topic: New to SQL Plus
Goto Forum:
  


Current Time: Sun Dec 04 10:21:08 CST 2016

Total time taken to generate the page: 0.07445 seconds