Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
kwacz23
Starting Member
44 Posts |
Posted - 2013-02-28 : 08:58:32
|
Hello Could you help me to accelerate a below query?UPDATE a_stg_value_descrription SET id_part_number = (SELECT id FROM (SELECT DISTINCT A.id , B.attribute_id FROM a_stg_PartNumber A , a_stg_value_descrription B WHERE A.attribute_id = B.attribute_id ) C WHERE c.attribute_id = a_stg_value_descrription.attribute_id) It tooks about 90 second Regards |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 09:05:11
|
Try one of these. I think these are logically equivalent to your queries, but please examine and test to see if they really are:UPDATE b SET id_part_number = a.IDFROM a_stg_value_descrription b INNER JOIN a_stg_PartNumber a ON a.attribute_id = b.attribute_id;;WITH cte AS( SELECT DISTINCT b.id_part_number, a.ID FROM a_stg_value_descrription b INNER JOIN a_stg_PartNumber a ON a.attribute_id = attribute_id)UPDATE cte SET id_part_number = id; |
|
|
kwacz23
Starting Member
44 Posts |
Posted - 2013-02-28 : 09:32:38
|
Unfortunately I got error message Msg 4403, Level 16, State 1, Line 1Cannot update the view or function 'cte' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 09:44:57
|
Remove the DISTINCT from the second query. You don't have to use both queries. Both are supposed to do the same thing; use whichever is faster and logically correct. |
|
|
|
|
|