Very Computer

I only want to update one row in a database.  However, my WHERE clause
affects two or more rows.  I cannot add any more conditions to the WHERE
clause to limit the number of rows affected.  This statement is for an Audit
type function which eliminates duplicate records that are created.

My SQL Statement:
UPDATE table SET columnx='1' WHERE id='1'

Is there a way to write an UPDATE SQL statement to limit the number of rows
that are updated?

PB,

SET ROWCOUNT 1
UPDATE ...
SET ROWCOUNT 0

Keep in mind that which of the two rows will be updated is being left open
to "chance". That is not a completely accurate statement, because it is not
really chance, but the point being that while you will update only one row
of the table, depending upon a number of factors, SQL Server might one day
update row #1, and some other day update row #2.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> I only want to update one row in a database.  However, my WHERE clause
> affects two or more rows.  I cannot add any more conditions to the WHERE
> clause to limit the number of rows affected.  This statement is for an
Audit
> type function which eliminates duplicate records that are created.

> My SQL Statement:
> UPDATE table SET columnx='1' WHERE id='1'

> Is there a way to write an UPDATE SQL statement to limit the number of
rows
> that are updated?

Yes, use a precise WHERE clause to limit the update to a (single) row.
--
Frederik Jensen, Judex

Make sure the id contains only unique ids (if you have '1'
in more than one row, more than one row will be updated).  
If the id column is not your primary key, you may consider
making it.  Also, I notice that in your sql statement you
have <snip>WHERE id = '1'<end>.  If the id column is
numeric, you're WHERE clause should look like this:

WHERE id = 1

No single quotes around the 1.

Quote:>-----Original Message-----
>I only want to update one row in a database.  However, my
WHERE clause
>affects two or more rows.  I cannot add any more

conditions to the WHERE

Quote:>clause to limit the number of rows affected.  This

statement is for an Audit

Quote:>type function which eliminates duplicate records that are
created.

>My SQL Statement:
>UPDATE table SET columnx='1' WHERE id='1'

>Is there a way to write an UPDATE SQL statement to limit
the number of rows
>that are updated?

>.