Confused about IN in sql queries (DELETE with subquery)

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Confused about IN in sql queries (DELETE with subquery)

Post by barry.marcus »

I'm confused why this:

<sql "select id crosstab_id from crosstab where filter_id=$thisFilterId">
<sql "delete from result where crosstab_id=$crosstab_id"></sql>
</sql>

does not give identical results as this:

<sql "delete from result where crosstab_id in (select id from crosstab where filter_id=$thisFilterId)"></sql>

The first does what I want it to do. Namely, it deletes all the rows from table "result" if the related row in table "crosstab" has the value $thisFilterId in the filter_id column. In one particular case there were 11 such rows in table "result". The second only deletes one row.

Is this related to the change in the IN operator in version 7, or has this behavior been there all along, and I've only just noticed it? I have no problem using the first code construct, but the subquery just "feels" more natural to me.

Thanks
User avatar
Kai
Site Admin
Posts: 1271
Joined: Tue Apr 25, 2000 1:27 pm

Confused about IN in sql queries (DELETE with subquery)

Post by Kai »

Both variants work here in version 7 with some made-up data. Are there indexes on crosstab(filter_id) and result(crosstab_id)? If you can still reproduce the issue (i.e. you have the source tables or can reconstitute them), open a tech support ticket.
Post Reply