Fucking SQL

[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

[Nymph]

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?

[sarettah]

Originally Posted by Nymph

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?

no..

A sub query in a where clause returns a set of records (just like a regular select query). You can compare a field (or fields if you concatenate them) and perform actions on the matching records.

So, say you have a list of names and addresses in a master table and in there you have a unique tag called cust_id that identifies any individual customer.

Now, because you know that you aren't going to get a phone number for all your customers you decided that that was a differnt table so you have a table called phones and in that table you have the phone number of those customers that gave you one and each phone record also has a tag called cust_id that is a matching tag to the cust_id field in your master table.

To see which customers had given you phone numbers you can use a subquery in the where clause. (you could also use a join and probably would if you were actually pulling the data but sometimes it is quicker to use a subquery) So, to get a count of customers that had phones I could use a query of:

"select count(*) from customers where cust_id in (select cust_id from phones)"

Now, before the xspurts jump in, yes, I could also have just counted distinct cust_ids in phones to get the same result. I am just trying to demonstrate how it works.

That make sense?

In the case where the accident occurred, I was trying to delete a selected set of records from a sub table.

There is a main table that has all the admin info and a vertical database for all the "other stuff". So, when I want to clean up test shit real quick I just use a delete query against the sub table where I pull the keys to delete from the master table.

In this case, I was moving too quickly and missed editing a table name in the query, so the table that the subquery was pointing to did not have the field I was trying to match to in it. Instead of throwing the error it instead deleted 18000 records.

I was running without a net in firehose mode too, my bad. But the query should have blown up, not deleted anything................. dammit

[Nymph]

Originally Posted by sarettah

no..

A sub query in a where clause returns a set of records (just like a regular select query). You can compare a field (or fields if you concatenate them) and perform actions on the matching records.

So, say you have a list of names and addresses in a master table and in there you have a unique tag called cust_id that identifies any individual customer.

Now, because you know that you aren't going to get a phone number for all your customers you decided that that was a differnt table so you have a table called phones and in that table you have the phone number of those customers that gave you one and each phone record also has a tag called cust_id that is a matching tag to the cust_id field in your master table.

To see which customers had given you phone numbers you can use a subquery in the where clause. (you could also use a join and probably would if you were actually pulling the data but sometimes it is quicker to use a subquery) So, to get a count of customers that had phones I could use a query of:

"select count(*) from customers where cust_id in (select cust_id from phones)"

Now, before the xspurts jump in, yes, I could also have just counted distinct cust_ids in phones to get the same result. I am just trying to demonstrate how it works.

That make sense?

Yes.
After I posted that, I took a look at our favorite recipe script to see how it pulled querys. I have 3 tables for each recipe, so it calls the recipeid from each table to edit, delete, or whatever I need to do with it.