help with update statement

[Bill]

I have a table (receiving), which contains tracking numbers. From time to time we get duplicate tracking numbers within this table. I had been using enterprise manager to edit those records with a unique constant, such as if the tracking #’s are 1ZY84323991, I would change one of them as 1ZY84323991*. I was wondering if there is a statement that I could use to edit the record via the query analyzer? I’ve tried

Set rowcount 1
Update receiving
Set tracking = ‘unique tracking #’
Where tracking = ‘tracking #’

But nothing happened, I’m I on the right track? (no pun intended)

[skhanal]

Do you get the same result without
SET ROWCOUNT

Try

begin tran
Update receiving
Set tracking = ‘unique tracking #’
Where tracking = ‘tracking #’


and see how many rows get updated. If you do not like what you see, rollback.

[Bill]

hey skhanal, without the rowcount I get 0 rows affected. I just tried your statement and still get 0 rows affected.

[MAK]

try this





Update receiving
Set tracking = 'unique tracking #'
Where ltrim(rtrim(tracking)) = 'tracking #'

and also see how many rows u really have with tracking ='tracking #'

select * from receiving where
tracking ='tracking #'

[Bill]

Thank you MAK, I tried yours and both records were changes .. so I added the
set rowcount 1 to it and wham-o got 1 row affected. Also thanks for that extra statement you included ...