Update SQL using joins

From: Phil <philaudio_at_philharmony.net>
Date: 21 Apr 2005 05:07:22 -0700
Message-ID: <1114085242.281200.104350_at_z14g2000cwz.googlegroups.com>

[Quoted] [Quoted] 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)

[Quoted] 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)

Received on Thu Apr 21 2005 - 14:07:22 CEST