quote:

---

Originally posted by RonanKeating

hi all,

Does anybody can help to spot where I'm making the mistake with following code, cause SSMSE 2005 complains about the incorrect syntax near the keyword "AND".

UPDATE e
SET e.prop_type = a.row_n
FROM (
SELECT d.elm_type, d.elm_prop,
ROW_NUMBER() OVER(ORDER BY d.elm_type ASC, d.elm_prop ASC) as row_n
FROM (
SELECT DISTINCT elm_type, elm_prop
FROM [elements]
WHERE prop_type IS NOT NULL
) d
) a
LEFT OUTER JOIN [elements] e
ON e.elm_type = a.elm_type,
AND e.elm_prop = a.elm_prop


Regards,

---

quote:

---

Originally posted by RonanKeating

thanks in advance that works now,

I already have SQL books and I'm studying it but there are still difficulties for my perception for example with the posted code.

Here is indented order and my interpretation for the posted code.
As far as I understand when interpreting I should start from inner-most brackets/paranthesis.

1. UPDATE e
2. SET e.prop_type = a.row_n
3. FROM (
4. SELECT d.elm_type, d.elm_prop,
5. ROW_NUMBER() OVER(ORDER BY d.elm_type ASC, d.elm_prop ASC) as row_n
6. FROM (
7. SELECT DISTINCT elm_type, elm_prop
8. FROM [elements]
9. WHERE prop_type IS NOT NULL
10. ) d
11. ) a
12. LEFT OUTER JOIN [elements] e
13. ON e.elm_type = a.elm_type,
14. AND e.elm_prop = a.elm_prop

A) 6. to 10. lines defines inner-most distinct table and provide the resulting dataset to 3. line SQL statement as variable "d".
B)3. line performs the selection based on the input from "d" result data set, and here it somehow implements the Row_Number custom function and returns the result as variable "row-n". Question comes in mind that can we call like that ORDER BY in 5.th line ?
C.) and all the returned dataset is returned as input to LEFT OUTTER JOIN as "a" variable.

I'm experiencing difficulties when trying to comrise the logic of that nested SQL statements. Could point out which is the best way?

Please comment on my interpretations

---