Re: Using Exists in Simple Update?

Lee E Parsons (lparsons_at_world.std.com) wrote:
: I have been working on a test case involving the following table structures
: and data.
 

: Table A (Key CHAR, data number)
: A NULL
: B NULL
: C NULL
 
: Table B (Key CHAR, data number)
: A 1
: B 2
: C 3
: A 1
: B 2
: C 3
 

: I'm trying to find the quickest way to set A.NUMBER = B.NUMBER. I can trust
: that for all cases of KEY, DATA will always be the same.

I don't know whether it is the quickest way, but it works.

SQL> create Table A (Key CHAR, data number);

Table created.

SQL> create Table B (Key CHAR, data number);

/* after inserting your data ... */

SQL> select * from A;

                       Softouch Information System Report

K       DATA                                                                    
- ----------                                                                    
A                                                                               
B                                                                               
C                                                                               

SQL> select * from B;
                       Softouch Information System Report

K       DATA                                                                    
- ----------                                                                    
A          1                                                                    
B          2                                                                    
C          3                                                                    
A          1                                                                    
B          2                                                                    
C          3                                                                    

6 rows selected.

SQL> update A

  2  set DATA =     
         (select DATA from B where A.KEY = B.KEY and rownum < 2);

3 rows updated.

SQL> select * from A;

                       Softouch Information System Report

K       DATA                                                                    
- ----------                                                                    
A          1                                                                    
B          2                                                                    
C          3                                                                    

SQL> exit

Hopo it helps.

Jozef Received on Wed Jul 12 1995 - 00:00:00 CEST