Re: Strange Timeout Error
(Guess it helps if I actually read the queries huh?)
You can't delete tblFactSales from tblFactSales.
You could DELETE * from tblFactSales WHERE something = somethingelse.
Re: Strange Timeout Error
How does "DELETE tblFactSales FROM tblFactSales" make any sense at all, especially given that you're then trying to extract values from this table in the same command? The mystery isn't that this doesn't work on the server but that it 'works' on SQL Express. I suspect that in fact it doesn't, it's just that there's no error generated.
Re: Strange Timeout Error
maybe I'm mistaken, but I thought "delete tblFactSales from tblFactSales" just means the same as DELETE * FROM tblFactSales. basically stating to DELETE all columns in the table.
Quote:
Originally Posted by
TCarter
(Guess it helps if I actually read the queries huh?)
You can't delete tblFactSales from tblFactSales.
You could DELETE * from tblFactSales WHERE something = somethingelse.
Re: Strange Timeout Error
the first query finds the unique values in tblFactSales, inserts them to #tmp, and the second query selects all the related records to #tmp that reside in tblFactSales.
Re: Strange Timeout Error
No 'maybe' about it! DELETE * means delete all values in all columns of the defined datarows. DELETE table means delete the whole table, schema and all so it clearly couldn't support a FROM and certainly not a selective one.
Re: Strange Timeout Error
Quote:
Originally Posted by
S37N
the first query finds the unique values in tblFactSales, inserts them to #tmp, and the second query selects all the related records to #tmp that reside in tblFactSales.
No. It doesn't!
Re: Strange Timeout Error
changing it to "DELETE * FROM tblFactSales RIGHT JOIN #tmp" gives me a syntax error near "*"
changing it to "DELETE tblFactSales RIGHT JOIN #tmp" gives me a syntax error near RIGHT.
any suggestions?
not that it matters, but the only reason I put the name in there is because I kept getting the syntax errors. also; i've ran the test locally several times, and verified results, so it's hard for me to believe that you're right.
Quote:
Originally Posted by
dunfiddlin
No 'maybe' about it! DELETE * means delete all values in all columns of the defined datarows. DELETE table means delete the whole table, schema and all so it clearly couldn't support a FROM and certainly not a selective one.
Re: Strange Timeout Error
Quote:
changing it to "DELETE * FROM tblFactSales RIGHT JOIN #tmp" gives me a syntax error near "*"
Yes it will. I allowed myself to be distracted by TCarter's reply. DELETE in SQL is a row based command so it automatically deletes *. It's just ... DELETE FROM tblFactSales etc.
Re: Strange Timeout Error
Quote:
Originally Posted by
dunfiddlin
Yes it will. I allowed myself to be distracted by TCarter's reply. DELETE in SQL is a row based command so it automatically deletes *. It's just ... DELETE FROM tblFactSales etc.
Yes, what will? I'm confused. I've tried that format, and it gives me a syntax error.
Re: Strange Timeout Error
I was sure the delete needed to specify which table to delete from when a join was present. How would it know which table to delete?
Re: Strange Timeout Error
Re: Strange Timeout Error
Well that's probably down to the multiple join attempt!
DELETE FROM table RIGHT JOIN table2 ON table.col = table2.col WHERE something = somethingelse
You join on one primary key which identifies a row in one table as 'belonging to' a row in the other table. You delete based on values defined in the where clause. But please note that the join means you will be deleting the row from both tables which, I assume is not what you intend.
If you simply want to select a number of rows into a temporary table and then delete them from the source table then you simply have to use exactly the same FROM and WHERE statements with which you selected with a DELETE command. I see no point in the tangled web your second query has become!
Re: Strange Timeout Error
let me backup a little, and explain why it looks strange...
this is a table from a cubed database (OLAP, I believe it's called/ see SAP BPC for more info), not a standard relational database. without getting into too much detail, there are records in the table, that relate to other records in the same table. There are base records (data sent by users), and logic records (records produced through code).
Here's how it works "in my head"...
my first query finds all of the "base" records that have short_tons >= -.01 and <=.01, and inserts them into #tmp table (unique identifiers are product, shipto, time, and datatype (these are columns in the tblFactSales table).
my second query uses the unique ID list from #tmp, and is joined back to tblFactSales so it can grab ALL of the related records.
I need to grab all of the related records, not just the short_ton records.
Re: Strange Timeout Error
So where does DELETE come into this? Time can't be a unique identifier because there is always a possibility (no matter how remote) that there may be duplicate values and unless they've invented an infinite number of types I really can't see how datatype can possibly be. In fact, if you have described the database correctly, it looks like there are no unique identifiers at all. As far as I can see any attempt to use such a database purely with standard SQL is doomed to failure from the get go.
I mean how do related records in the same table make any sense anyway? Do values get repeated? Is there a subset of columns filled in one record and a completely different subset in others? SQL is designed for plain and simple tables in relation to each other which can be joined on a single identifying common denominator. I just don't know whether it can begin to cope with concepts such as inner relations and joining tables to subsets of themselves.
Re: Strange Timeout Error
turns out, I just needed to extend the CommandTimeout setting. running over the network (on the larger db) was taking longer than 30 seconds, which is the default.