Update Once 1

[curvv]

Hi,

Say i've got in a table two records both with a name value of Joe. I want to update ONE of these records(doesn't matter which). Is it possible to have something like:

UPDATE ONCE mytable SET country='ZA' WHERE name='Joe'

thanx
Chris ######## CtN ########

 

[cmmrfrds]

You could do it through a query that checks for duplicates.

Here is a quick way by setting rowcount to 1 for the duration of the update.

set rowcount 1
go
UPDATE ONCE mytable SET country='ZA' WHERE name='Joe'
go
set rowcount 0
go

 

[curvv]

hi,

will it not work if i only use:

UPDATE ONCE mytable SET country='ZA' WHERE name='Joe' ??

thanx
Chris ######## CtN ########

 

[bperry]

There is no Update ONCE syntax in SQL Server. (Perhaps that is an Oracle syntax?)

 

[curvv]

hi,

i don't quite understand howto use cmmrfrds's example above.

could you explain it to me please?

thanx
chris ######## CtN ########

 

[bperry]

cmmrfrds's example should work fine; it is a little 'batch' of commands that you would execute as one command, typically from Query Analyzer. Just key them in, execute them as one batch, and there you go!

If you want to do this from some other client software, like VB or ASP, then that solution might not be a great fit for you, as you are usually issing just one 'command' from the client, not a bunch of statements together.

One thing that wasn't clear to us is how many records you need to get updated. i.e. is it just records where name='Joe'? (Probably not, but if so, I might just go into EM and do it!).

If you have bunches of records to do, then perhaps something else could be suggested. There are some well-known workarounds for updating 'just one' of a bunch, and they often depend on examing another column to determine 'which record' to update. (This is usually a date column or a PkID).

If you explain your situation a little more, perhaps cmmrfrd or myself will make a suggestion to you.

 

[curvv]

i'm using asp. i basically want sql to go to the first row it finds where the value of name is 'Joe' and update that row with the new values and then stop. ######## CtN ########

 

[bperry]

Okay. As I mentioned, the way that is usually done is to examine some other column to help identity which row to update (and which row(s) not to update!)
For example, here is often the easiest situation: I use the PkId column to help identify the row.

Update myTable
Set country='ZA'
Where PkId =
(Select TOP 1 PkId
from myTable Where name='joe')


In other cases, a date column is often used to update, say, the earliest row of a bunch. If that's the situation you have, myself (or someone else) could post an example of that. It all depends on what the rest of your table looks like.

Hope this gives you some ideas,
brian perry

 

[curvv]

thanx alot. i get the idea. i'll go give it a go. ######## CtN ########