Re: Update SQL using joins

Phil schrieb:
> I'm seriously missing something here and i'm wondering if someone can
> help me understand it.
>
> I have 2 tables. Table p contains 1,400 records, table m contains
> 470,000 records. I want to modify the 1,400 records in table m that
> corrsepond to all the 1,400 records in table p. Here's what i've
> tried,
>
> UPDATE m
> SET len = (SELECT units
> FROM p
> WHERE m.bkseq = p.bkseq)
>
> I thought that the WHERE clause would ensure that only records with the
> same bkseq in both tables would be changed, = 1,400. In stead 470,000
> records in m are changed. Why? Do i really have to add a second where
> clause at the end,
>
> WHERE bkseq IN (SELECT bkseq
> FROM p
> WHERE p.bkseq = m.bkseq)
>

Hi, if the bkseq column is unique in both tables ( in terms of enabled unique constraints ), then you could rewrite your update as

UPDATE (SELECT P.UNITS,M.LEN
FROM M,P
WHERE P.BKSEQ = M.BKSEQ)
SET LEN = UNITS; Additional benefit is a little better execution plan , if your tables are significant large for your hardware - with provided example i assume  rather not - then you can ever use hash hint in the select... (if the mentioned uniqueness is not enforced , you will run into ORA-01779)

Best regards

Maxim Received on Sat Apr 23 2005 - 00:29:29 CEST