Home » SQL & PL/SQL » SQL & PL/SQL » update changes all records!
update changes all records! [message #250081] Sat, 07 July 2007 14:03 Go to next message
FrankBerlin
Messages: 5
Registered: July 2007
Location: Berlin, Germany
Junior Member
Hi all,

I searched the docu and several forums but I didn't find the solution for my problem:
When I update table A with values from table B and B contains less records then A, then all records in A are updated anyhow.
How do I avoid this (without using 'merge')?

Thanks in advance
Frank

CREATE TABLE  HOSTINFO	
   (    SWITCHID        NUMBER, 
        MAC             VARCHAR2(15), 
        IPADDR          VARCHAR2(15), 
        HOSTNAME        VARCHAR2(80)
   );

CREATE TABLE  ARP 
   (    SWITCHID        NUMBER, 
        MAC             VARCHAR2(15), 
        IPADDR          VARCHAR2(15)
   );

Insert into HOSTINFO    (SWITCHID,MAC,IPADDR,HOSTNAME)  values (1,'0000.c0d1.c7fb',null,null);
Insert into HOSTINFO    (SWITCHID,MAC,IPADDR,HOSTNAME)  values (1,'0100.1234.5678',null,null);
Insert into HOSTINFO    (SWITCHID,MAC,IPADDR,HOSTNAME)  values (1,'0000.abcd.1234',null,null);
Insert into HOSTINFO    (SWITCHID,MAC,IPADDR,HOSTNAME)  values (1,'1234.5678.abcd',null,null);
Insert into HOSTINFO    (SWITCHID,MAC,IPADDR,HOSTNAME)  values (1,'0080.1a2b.3c4d',null,null);

Insert into ARP         (SWITCHID,MAC,IPADDR)           values (1,'0000.c0d1.c7fb','192.168.0.1');
Insert into ARP         (SWITCHID,MAC,IPADDR)           values (1,'0100.1234.5678','192.168.0.2');
Insert into ARP         (SWITCHID,MAC,IPADDR)           values (1,'1234.5678.abcd','192.168.0.3');

commit;

FRANK@XE >select * from hostinfo;

  SWITCHID MAC             IPADDR          HOSTNAME
---------- --------------- --------------- -----------
         1 0000.c0d1.c7fb
         1 0100.1234.5678
         1 0000.abcd.1234
         1 1234.5678.abcd
         1 0080.1a2b.3c4d

5 rows selected.

FRANK@XE >select * from arp;

  SWITCHID MAC             IPADDR
---------- --------------- ---------------
         1 0000.c0d1.c7fb  192.168.0.1
         1 0100.1234.5678  192.168.0.2
         1 1234.5678.abcd  192.168.0.3

3 rows selected.

FRANK@XE >select h.switchid, h.mac, a.ipaddr from hostinfo h, arp a where h.switchid=a.switchid and h.mac=a.mac;

  SWITCHID MAC             IPADDR
---------- --------------- ---------------
         1 0000.c0d1.c7fb  192.168.0.1
         1 0100.1234.5678  192.168.0.2
         1 0000.abcd.1234
         1 1234.5678.abcd  192.168.0.3
         1 0080.1a2b.3c4d

5 rows selected.

FRANK@XE >update hostinfo h set ipaddr = (select a.IPADDR from arp a where h.switchid=a.switchid and h.mac=a.mac);

5 rows updated.

FRANK@XE >select * from hostinfo;

  SWITCHID MAC             IPADDR          HOSTNAME
---------- --------------- --------------- ----------
         1 0000.c0d1.c7fb  192.168.0.1
         1 0100.1234.5678  192.168.0.2
         1 0000.abcd.1234
         1 1234.5678.abcd  192.168.0.3
         1 0080.1a2b.3c4d

5 rows selected.

This is just for verification:
FRANK@XE >update hostinfo h set ipaddr = substr(rowid,1,10);

5 rows updated.

FRANK@XE >select * from hostinfo;

  SWITCHID MAC             IPADDR          HOSTNAME
---------- --------------- --------------- ----------
         1 0000.c0d1.c7fb  AAAFqHAAEA
         1 0100.1234.5678  AAAFqHAAEA
         1 0000.abcd.1234  AAAFqHAAEA
         1 1234.5678.abcd  AAAFqHAAEA
         1 0080.1a2b.3c4d  AAAFqHAAEA

5 rows selected.

FRANK@XE >update hostinfo h set ipaddr = (select a.IPADDR from arp a where h.switchid=a.switchid and h.mac=a.mac);

5 rows updated.

FRANK@XE >select * from hostinfo;

  SWITCHID MAC             IPADDR          HOSTNAME
---------- --------------- --------------- ----------
         1 0000.c0d1.c7fb  192.168.0.1
         1 0100.1234.5678  192.168.0.2
         1 0000.abcd.1234
         1 1234.5678.abcd  192.168.0.3
         1 0080.1a2b.3c4d

5 rows selected.
Re: update changes all records! [message #250082 is a reply to message #250081] Sat, 07 July 2007 14:12 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
By using a WHERE clause on the UPDATE statement itself.
Re: update changes all records! [message #250083 is a reply to message #250081] Sat, 07 July 2007 14:47 Go to previous messageGo to next message
FrankBerlin
Messages: 5
Registered: July 2007
Location: Berlin, Germany
Junior Member
ok,

but that's my problem. Probably I'm to stupid, but I don' get it work.
Would you be so kind and show me how to this for my example?

Frank
Re: update changes all records! [message #250089 is a reply to message #250083] Sat, 07 July 2007 16:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
General syntax for updating only the rows you want, without setting column to null in all other rows:

UPDATE some_table
SET some_column = (some corrolated subquery)
WHERE EXISTS (same corrolated subquery as above);
Re: update changes all records! [message #250093 is a reply to message #250081] Sat, 07 July 2007 17:18 Go to previous messageGo to next message
FrankBerlin
Messages: 5
Registered: July 2007
Location: Berlin, Germany
Junior Member
Hello Barbara,

thank you - "where exists" is what I needed !
Now it looks so easy to me, but I was really confused of all the "where clauses" and variations on subqueries an so on ...

best regards
Frank

ps:
Now I use:
update hostinfo h set ipaddr = (select a.IPADDR from arp a where h.switchid=a.switchid and h.mac=a.mac)
  where exists (select switchid, mac from arp a where h.switchid=a.switchid and h.mac=a.mac)

Re: update changes all records! [message #250661 is a reply to message #250081] Tue, 10 July 2007 14:21 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
just an fyi,

on an exists (or not exists), the columns that are returned by the sub query are ignored. For example, your update query can be written as the following

update hostinfo h set ipaddr = (select a.IPADDR from arp a where h.switchid=a.switchid and h.mac=a.mac)
  where exists (select null from arp a where h.switchid=a.switchid and h.mac=a.mac);


Personally I think this is clearer and won't confuse someone reading your code. That being said, nice job.
Re: update changes all records! [message #250663 is a reply to message #250081] Tue, 10 July 2007 14:35 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
FrankBerlin wrote on Sat, 07 July 2007 14:03
Hi all,

...

FRANK@XE >select h.switchid, h.mac, a.ipaddr from hostinfo h, arp a where h.switchid=a.switchid and h.mac=a.mac;

  SWITCHID MAC             IPADDR
---------- --------------- ---------------
         1 0000.c0d1.c7fb  192.168.0.1
         1 0100.1234.5678  192.168.0.2
         1 0000.abcd.1234
         1 1234.5678.abcd  192.168.0.3
         1 0080.1a2b.3c4d

5 rows selected.

...



this is really strange result for me on the data you provided.
Looks like inner join and therefore it should output only 3 rows, but not 5 as you have shown.
Re: update changes all records! [message #250677 is a reply to message #250661] Tue, 10 July 2007 15:54 Go to previous message
FrankBerlin
Messages: 5
Registered: July 2007
Location: Berlin, Germany
Junior Member
Hi,

I'm happy about every contribution. And this one is very notable.
I will verify that for myself ...
... but obviously you are right and oracle just gives back a set of "rowids" or something like that Smile and not the referenced columns (respectively values).

Thank you
Frank


Previous Topic: Simple way of doing spooling within spooling?
Next Topic: partition exchange
Goto Forum:
  


Current Time: Fri Dec 02 18:58:13 CST 2016

Total time taken to generate the page: 0.43985 seconds