CODEWITHSUNDEEP
c#sql-serverwinforms
AJ26
AJ26

Reputation: 501

Editing duplicate values in a database

I have a DataGrid View pulling some items from my database. What I want to achieve is to be able to edit the pack size or the bar_code fields. I am aware on how to update values in a database but how would I go about doing it if the data is the same? Meaning in many instances a bar code would have multiple pack sizes that is related to the one bar code number. Let's say I have the below screenshot. A data entry error was made and the bar_code and PackSize columns are the exact same. I want to change the first bar code to "1234." How would I achieve this? I can't say update barcode to 'textBox1.Text' where bar_code = '771313166386' because it would then change both data. How do I go about only focusing on one row of data at a time?

enter image description here

Upvotes: 0

Views: 270

Answers (5)

Peter Abolins
Peter Abolins

Reputation: 1539

A data entry error was made and the bar_code and PackSize columns are the exact same

I think this is the key. Essentially, the exact duplicates are unintentional, and the rows should be unique. Further it looks like bar_code + pack_size is your primary key (subject to data being entered correctly).

So, when you do an update, simply update the first row found that matches a bar_code and a pack_size. If it isn't unique, then the update should ensure that you are one step closer to unique rows in the database.

If you need a non-verbal answer, let me know.

Upvotes: 0

Sunil Singhal
Sunil Singhal

Reputation: 603

You need to uniquely represent the products. As per your sample data, I guess that there isn't any primary key on your table. What you can do is either specify a unique constraint on columns to ensure that this type of data entry cannot be done. If you cannot come up with list of columns to uniquely identify the rows, you can use surrogate keys by specifying Identity column and then while updating, always put a constraint where thisIdentityColumn=value

Upvotes: 1

user8608631
user8608631

Reputation:

This might not help you directly in your answer. But, it is important to mention that your table design is incorrect. You should ensure the data integrity by creating a primary key in your table.

So when you need to update a product you have only one row to update.

Then you can add more tables and use foreign key references between them.

Upvotes: 1

Maverick Sachin
Maverick Sachin

Reputation: 883

I'd suggest you handle the logic of data duplicate manipulation at the backend rather than pulling them inside the grid and handle it there.

The following query will help you retrieve the duplicate records based on the mentioned columns. You can change it to UPDATE or DELETE as per your requirement.

-- Using cte and ranking function
    ;With CTE
    As
    (
        Select 
            Product,
            Description,
            BarCode,
            PackSize
            Row_Number() Over(Partition By Product, BarCode, PackSize Order By Product) As RowNum
        From YourTable
    )
    Select * From CTE
    -- Where RowNum > 1;

Hope this is helpful :)

Upvotes: 1

lubilis
lubilis

Reputation: 4160

You can try using this query to update only the first row:

UPDATE TOP (1) my_table
SET bar_code = '1234'
WHERE bar_code = '771313166386'

You should have an auto-increment id column or a Primary key in your table.

Upvotes: 2

Related Questions