Re: update from table prob

Response is inline, at the update query. In article <7ufi4u$o0s$1_at_nnrp1.deja.com>,   pdiesner_at_my-deja.com wrote:
> Hi folks,
> take a look at this:
>
> select count(*) from temp
>
> result: 1700
>
> select count(*) from temp t, person p where t.person_id = p.person_id;
>
> result: 1700
>
> select sum(count(*)) from person
> group by person_id having count(*) = 1
>
> result: 2500
>
> update person p set p.a_date =
> (select to_date('31.12.1997', 'DD.MM.YYYY') from temp
> where person_id = p.person_id)
>

This query set the “a_date” to be equal to '31.12.1997' when there was a match between the person table and the temp table. So far so good. If there was no match between the two tables, then “a_date” is set to NULL. In order to limit the update (and speed up the query), place a where clause at the update. I.E.

UPDATE person p SET p.a_date = to_date('31.12.1997', 'DD.MM.YYYY') WHERE person_id IN (SELECT person_id FROM temp)

HTH
    James

> 2500 rows updated !!!
>
> How can this be ? I'm expecting, that the update-query updates
> all rows in table person with an person_id that occurs in table temp
> (1700 > rows).
> But obviously all rows (2500) in table person are updated.
>
> Any ideas / suggestions ?
>
> Thanks
>
> Peter
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 18 1999 - 11:50:32 CDT