May 16, 2012 at 7:34 am
I was wondering how will be the precedence of operation in following query:
UPDATE table1 t1
SET t1.col1 = (SELECT TOP 1 t0.col0 FROM table0 t0
WHERE t0.date_of_call < t1.date_of_call
AND (t0.dur_connection = t1.dur_network OR
t0.dur_connection = t1.dur_network - 1 OR t0.dur_connection = t1.dur_network + 1 OR
t0.dur_connection = t1.dur_network - 2 OR t0.dur_connection = t1.dur_network + 2 OR
t0.dur_connection = t1.dur_network - 3 OR t0.dur_connection = t1.dur_network + 3)
AND t0.caller_id = t1.caller_id
ORDER BY t0.date_of_call desc)
What I want to achieve here is to update t1.col1 with t0.col0 where the gap between t0.dur_connection (current record) and t1.dur_network (some previous record) is the smallest.
So in case of multiple match, will the db engine first treat the smallest gap, where difference is 0, then where difference is +1/-1, then where difference is +2/-2 and so on? Or will it treat it randomly?
Thanks for your reply,
Natha
May 16, 2012 at 8:20 am
natha (5/16/2012)
I was wondering how will be the precedence of operation in following query:
UPDATE table1 t1
SET t1.col1 = (SELECT TOP 1 t0.col0 FROM table0 t0
WHERE t0.date_of_call < t1.date_of_call
AND (t0.dur_connection = t1.dur_network OR
t0.dur_connection = t1.dur_network - 1 OR t0.dur_connection = t1.dur_network + 1 OR
t0.dur_connection = t1.dur_network - 2 OR t0.dur_connection = t1.dur_network + 2 OR
t0.dur_connection = t1.dur_network - 3 OR t0.dur_connection = t1.dur_network + 3)
AND t0.caller_id = t1.caller_id
ORDER BY t0.date_of_call desc)
What I want to achieve here is to update t1.col1 with t0.col0 where the gap between t0.dur_connection (current record) and t1.dur_network (some previous record) is the smallest.
So in case of multiple match, will the db engine first treat the smallest gap, where difference is 0, then where difference is +1/-1, then where difference is +2/-2 and so on? Or will it treat it randomly?
Thanks for your reply,
Natha
I don't really understand your question but your update is highly likely not what you want. You are updating the entire table to a single value (there is no where clause on your update statement).
I don't understand your question about multiple matches at all. Your subquery is only going to return 1 row, the one with the first t0.date_of_call. If you meant multiple matches on that one it wouldn't actually matter which row is chose because the value is the same.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2012 at 9:15 am
the first one orderd by to.date of call - if there are multiples at the same time then it will depend on the internal storage in the table t0
if there is a clustered index then this will most likely determine the reocrd returned
the OR statements have no bearing on the 1st record returned, they simply filter in/out ther records you want. Since the OR statement are also inside parentethis they do not corrupt the AND statements
Parenthesis are evaluated first and then ANDED with your other critera
MVDBA
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply