update Query with Order by Clause- SQL Server2008

[Paramu1973]

I have a update query, But need with Order by Clause. I cannot identify the error.... update sales_master set un_rate = (select qtnmaster.un_rate where qtnmaster.itemcode=sales_master.itemcode order by (0+qtnmaster.Qtnno)) Thanks For The Ideas... :thumbsup:

[Mycroft Holmes]

Why on earth would you need an order by in an update clause (illegal for a start) you are identifying the individual records to update!

Never underestimate the power of human stupidity RAH

[i j russell]

You can use an ORDER BY clause in an UPDATE statement as long as it is in a subquery.

[i j russell]

Have a look at CROSS APPLY.

[loyal ginger]

There is no problem with the order by clause. However, your subquery may return multiple values, which makes the assignment not valid. You need to refine your subquery so that only the desired value is returned. For example, when you use order by clause and use "top 1", then the "un_rate" of the record with the smallest "Qtnno" value is returned. Without a clear knowledge of your original intension, I don't have a clear answer to your question.

modified on Tuesday, January 12, 2010 11:02 AM

[Corporal Agarn]

In T-SQL

Update sales_master
set un_rate = qtnmaster.un_rate
from sales_master
inner join qtnmaster
ON sales_master.itemcode = qtnmaster.itemcode

[i j russell]

This will only work if there are no more than 1 record in qtnmaster per itemcode.