On 12/18/06, Thomas H. <[EMAIL PROTECTED]> wrote:
>> oups. just thumbled over this as well when i forgot a FROM in a WHERE ...
>> IN
>> (....) and damaged quite some data. the bad query went like this:
>>
>> SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE
>> mov_name like '%, %' LIMIT 2)
>>
>> the subselect is missing a FROM <table>. in that case, pgsql seemed to
>> also
>> ignore the LIMIT 2 and returned 3706 records out of ~130000...
>
> and the UPDATE was?
that was done by the application with the returned recordset.
> also the limit applies only to the subselect, it has nothing to do
> with the upper query so the upper query can return more than number of
> rows specified in the subselect...
IF the subquery would only have returned 2 ids, then there would be at most
like +/-10 records affected. each mov_id can hold one or more (usuals up to
5) names. but here, the subquery seemed to return ~3700 distinct mov_ids,
thus around 37000 names where damaged by the following programmatical
updates instead of only a hands full...
have you tested the query in psql?
what results do you get?
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
