Hi everyone,
I have 2 tables #TABLE and #DETAIL_TABLE in that the second table has too many rows for each one of #TABLE's records. I have to update #TABLE by choosing only one value from #DETAIL_TABLE for each one of records in #TABLE (for each field, sorting
logic is different). Following query works fine(probably SQL server optimizes it, if any way exists) but I'm not sure if it is the better way of doing it:
UPDATE
#TABLE
SET
COLUMN_VALUE1 =
(SELECT
TOP 1 a.COLUMN_VALUE1
FROM #DETAIL_TABLE a
WHERE a.FOREIGN_KEY
= #TABLE.KEY_VALUE
ORDER
BY a.RANKED_FIELD1),
COLUMN_VALUE2 =
(SELECT
TOP 1 a.COLUMN_VALUE2
FROM #DETAIL_TABLE a
WHERE a.FOREIGN_KEY
= #TABLE.KEY_VALUE
ORDER
BY a.RANKED_FIELD2),
COLUMN_VALUE3 =
(SELECT
TOP 1 a.COLUMN_VALUE3
FROM #DETAIL_TABLE a
WHERE a.FOREIGN_KEY
= #TABLE.KEY_VALUE
ORDER
BY a.RANKED_FIELD3),
...
I prefer not use CURSOR , also do not like to break above query to multiple smaller INNER JOIN updates. Anyone can tell me what is the better way doing above regardingly?
Thanks
Amir