how to update only 1 item in a duplicate field in sql 2005

Question

have a duplicate fields and i need to update only one row how do i do this with sql 2005?

my database is as seen below:

+----------------+-------+-------------+------------------+---------------+
| Transaction_no | User  |  Check-In   |    Check-Out     |    barcode    |
+----------------+-------+-------------+------------------+---------------+
| 01-2013        | User1 |  --/--/--   | 12/28/2013 11:10 | APH009300L030 |
| 01-2013        | User1 |  --/--/--   | 12/28/2013 11:10 | APH009300L030 |
| 01-2013        | User1 |  --/--/--   | 12/28/2013 11:10 | APH009300L030 |
| 01-2013        | User1 |  --/--/--   | 12/28/2013 11:10 | APH009300L030 |
+----------------+-------+-------------+------------------+---------------+

if i update this 01-2013 | User1 | --/--/-- | 12/28/2013 11:10 | APH009300L030 all of the other data will be updated since there is no unique identifier for each of them — Ron, Dec 28 '13 at 04:50
do you hv any specific criteria for selecting any one row among duplicate ?also there is always some pattern for duplicate data also. your sample data do not follow any pattern.Because of which in @Vigness query all column has been use in partition.you are not clear. Why in first place there is duplicate ?why no validation before insert ? — KumarHarsh, Dec 29 '13 at 14:11
i did not add any validation during insert because there are certain cases that multiple same item may be check out at once. I cannot do it by quantity because what if i check out 3 items and returned only 2 then someone will checkout again that type of item. then that one that hasn't been checked in will be difficult to trace (sory for my bad english) but as they suggest i added a primary column which will act as a unique identifier for the duplicates. — Ron, Jan 03 '14 at 09:56

score 0 · Accepted Answer · edited May 23 '17 at 11:49

0

Try Like this

;WITH NumberedTbl AS 
 (
  SELECT ROW_NUMBER() OVER (
    PARTITION User,Check-In,Check-Out,barcode,Transaction_no
    ORDER BY User,Check-In,Check-Out,barcode,Transaction_no
  ) AS RowNumber,
  User,Check-In,Check-Out,barcode,Transaction_no
  FROM tbl
)

UPDATE T
SET Check-In= GETDATE()
FROM NumberedTbl T
WHERE T.RowNumber = 1

SELECT * FROM tbl

Please see Demo here

Source

edited May 23 '17 at 11:49

Community

1
1

answered Dec 28 '13 at 04:41

Vignesh Kumar A

27,863
13
63
115

i already tried the code above but when i update the field it updates all the others since there are no unique identifier – Ron Dec 28 '13 at 04:48
Still doesn't work maybe adding a new field as a unique identifier maybe its really impossible to get it without an identifier – Ron Dec 28 '13 at 05:02
Still id dint work means, you need to add one Primay key to your table. This is my suggestion – Vignesh Kumar A Dec 28 '13 at 05:03
yes i added a primary key. thanks for the help :) (sorry for the late reply i was out on vacation ) – Ron Jan 03 '14 at 09:58

score 0 · Answer 2 · answered Dec 28 '13 at 04:42

0

What you need in your table is a primary key column. You can just add an identity field. Then you'll be able to easily write queries like the one you need.

answered Dec 28 '13 at 04:42

Z .

12,657
1
31
56

is it ok if i use a autoincrement ID as a unique identifier? – Ron Dec 28 '13 at 04:49

score 0 · Answer 3 · answered Dec 28 '13 at 05:10

RAnking functions like row_number , rank , dense_rank wont work as all th field values are same and applying ORDER BY on any column after partitioninh will generate same rank for all rows.

Instead add another column habing unique values eg Identity column qfter which you can use this columm in ORDER By clause.

how to update only 1 item in a duplicate field in sql 2005

3 Answers3