Quote:
Originally Posted by sarettah
ok, i thought i was going to find out that this was an issue in just SQLServer but I just tested and it is an issue in MYSql too. Probably all known about but I just discovered it so I am going to vent a bit.
Ok, you have two tables.
Table1:
Trans_id key
all the other fields
Table2:
Rec_id key
all the other fields
If you say "select trans_id from table2" you get an error, as you should because the column trans_id is not in table2.
However, if you run that as a sub query in a where clause it does not return an error. I am not sure what it returns (i suspect null) but whatever the fuck it returns matches fucking everything.
So a query like "delete from table1 where trans_id in (select trans_id from table2)" where you would expect it to throw an error instead matches every fucking record in table1 and hence deletes them all.
i did this originally, by accident, in SQLServer yesterday, wiping out 18,000 records when I expected to delete about 10.
I tested in MYSql just a minute ago on my server and it worked the same way.
i also tested in FoxPro on my desk top. It threw an error telling me that the trans_id column was missing. i love my FoxPro, i don't give a shit what anyone else says about it.
Dammit
|
Trying to understand this here....
You have one record, that has data in both tables? Couldn't you do a double query assuming that not all records would have the same data info in each table?