[RESOLVED] Update Certain Rows

obrienkev

Hi all,

How do I ensure that only a certain 20 rows are updated in SQL Server table?

Here's my query...

$query3 = "UPDATE training ".
		 "SET firstname = '$firstname', ".
		 "surname = '$surname' ".
		 "WHERE ffnumber = '$ffnumber'";
//echo $query3;
$result3 = mssql_query($query3) or die('Update Query Error');

rincewind456

If you just want to update any/the first 20 rows the database finds then use LIMIT 20, if you want to update a specific 20 rows then you will have to narrow down the scope of your query to find the correct 20 rows.

obrienkev

I'm using SQL Server so LIMIT doesnt work on that.

rincewind456

Then maybe it supports ROWCOUNT, sorry not to hot on MS software.

Roger_Ramjet

obrienkev wrote:
I'm using SQL Server so LIMIT doesnt work on that.

No it uses TOP instead, and so you need to use a subquery as well.

 $query3 = "UPDATE training ".
         "SET firstname = '$firstname', ".
         "surname = '$surname' ".
         "WHERE train_id IN (SELECT TOP 20 train_id FROM training WHERE ffnumber = '$ffnumber')"; 

// OR
 $query3 = "UPDATE training t INNER JOIN ".
         "(SELECT TOP 20 * FROM training WHERE ffnumber = '$ffnumber') AS x ".
         "USING (train_id) ".
         "SET t.firstname = '$firstname', ".
         "t.surname = '$surname'";

Always assuming that you have an id column in the trainig table of course.

obrienkev

Thanks Roger and ricewind.