Update only x records but also depending on a given order.

25 Nov 2005 3:35 PM

Harry Leboeuf

We are looking for a way to update (ex) the 5 first rows of a table
depending some selections and a order by clause.

ex (of a non working query)

set rowcount = @arowcount

update t1 set c1 = @something

from t1

where c2 = @sm2

order by c3

anybody has an idea on how to to this

we'v tried with a subquery, select top 1000 from ... order by, byt the
problem is that the order of the subquery is sorted, but you have no
guarantie that the update will pick the first records of the subquery. And a
top x as a variable is also inpossible.

We would prefer not to do it with dynamic sql as this would be to slow.

25 Nov 2005 3:49 PM

Anith Sen

If you have a key in your table t1, the UPDATE statement formulation is
easy. Essentially it is using a WHERE clause or a correlation in a subquery
used in queries similar to the ones with ranking approaches often
recommended in these newsgroups. If you need specific help, please post your
table structures, sample data & expected results ( for details refer to:
www.aspfaq.com/5006 )

--
Anith

25 Nov 2005 4:22 PM

Stephen Howe

> anybody has an idea on how to to this
>
> we'v tried with a subquery, select top 1000 from ... order by, byt the
> problem is that the order of the subquery is sorted, but you have no
> guarantie that the update will pick the first records of the subquery

What do you mean "the first records of the subquery"?
Records are ordered in some way, usually by PK but if the result of a Hash
JOIN then they can be any order.

Are you saying you want the update to update a random selection of 5
records?
If you are not saying that, then you better say what records you do want
affected.
"first records of the subquery" is meaningless, does not specify what order
you want.

Stephen Howe

25 Nov 2005 8:45 PM

David Portas

Show quote

"Harry Leboeuf" <extrem***@hotmail.com> wrote in message
news:O%23Hu0Xd8FHA.1028@TK2MSFTNGP11.phx.gbl...
>
> We are looking for a way to update (ex) the 5 first rows of a table
> depending some selections and a order by clause.
>
> ex (of a non working query)
>
> set rowcount = @arowcount
>
> update t1 set c1 = @something
>
> from t1
>
> where c2 = @sm2
>
> order by c3
>
>
>
> anybody has an idea on how to to this
>
> we'v tried with a subquery, select top 1000 from ... order by, byt the
> problem is that the order of the subquery is sorted, but you have no
> guarantie that the update will pick the first records of the subquery. And
> a top x as a variable is also inpossible.
>
> We would prefer not to do it with dynamic sql as this would be to slow.
>
>

Here's an untested guess:

UPDATE T1
SET c1 = @something
WHERE T1.c2 = @sm2
AND 5 >
(SELECT COUNT(*)
   FROM T1 AS T2
   WHERE T2.c2 = T1.c2
    AND T2.c3 < T1.c3) ;

For this to work (c2,c3) had better be unique otherwise the result may be
more or fewer than 5 rows updated. If you need more help, please post DDL,
sample data and required end results so that we don't have to guess.

--
David Portas
SQL Server MVP
--