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...
LIMIT is often meaningfull only in conjuction with ORDER BY
yep but not here. all i wanted to do is to get names from 2 movies and run
an *observed* edit on them.
what did pgsql actually do with that subquery? did it return all records for
which mov_name match '%, %'?
- thomas
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
