How to Update duplicate records in table

Question

Post reply

How to Update duplicate records in table

svhanda

Old Hand

Points: 309
More actions
April 29, 2008 at 5:13 am

#187074

Hi All..
I have problem updating table having duplicate records..
Suppose one table having entries as
EMP ID EDUCATION
------ -------------
100 B.Com
100 MBA
so here i have duplicate EMP ID and i need to update EDUCATION column...updated data is in other table from where i need to match which emp id need to be updated...
its like i have master table having all records and other table where updated data is there and i need to update master record..
Pls help in this query..

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

do you want Education column to be updated to the same value?

can you post the table structure and some matching sample data for the other table ?

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 2

[font="Verdana"]Do you mean something like this ... ?

Update OldTable

Set OldTable.EmpId = NewTable.EmpId

From OldTable Inner Join NewTable On OldTable.Education = NewTable.Education

confirm on this.

Mahesh

[/font]

MH-09-AM-8694

svhanda Old Hand Points: 309 More actions · Answer 3

steveb (4/29/2008)
do you want Education column to be updated to the same value?
can you post the table structure and some matching sample data for the other table ?

Hi...

Thanks for reply,

My query is suppose there is table like

EMP_ID Comp_ID

100 1234

100 2345

here for same employee comp_id is different..now i am fetching updated value from SAP..so if COMP_ID will get changed..

say..

EMP_ID Comp_ID

100 4567

100 9876

so how should i write stored procedure so that distinct value of same EMP_ID will get updated...

Pls reply...

steveb. SSC-Forever Points: 46733 More actions · Answer 4

so EMP_ID =100 needs to have different values for Comp_Id,

4567, 9876 ?

do the tables have a primary key?

vblllove Ten Centuries Points: 1273 More actions · Answer 5

HI, Im using something like that But Im handling in the Code:

protected void EditStaffRoles()

{

try

{

//Create Datatable for Staff

string StaffId = ddlstaffname.SelectedItem.Value;

SqlConnection sqlcon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString());

string ss = "select * from PersonRoles where PersonId='" + StaffId + "'";

SqlDataAdapter sqlda = new SqlDataAdapter(ss, sqlcon);

SqlCommandBuilder cmdb = new SqlCommandBuilder(sqlda);

CheckBoxList ck = (CheckBoxList)dvContractorEdit.FindControl("cbrol");

DataTable dt = new DataTable();

sqlda.Fill(dt);

foreach (ListItem li in ck.Items)

{

if (li.Selected == true)

{

DataRow dr = dt.NewRow();

dr["PersonId"] = Convert.ToInt32(StaffId);

dr["RoleId"] = Convert.ToInt32(li.Value);

dt.Rows.Add(dr);

}

sqlda.Update(dt);

this.lblerror.Text = "User has been updated";

this.ddlstaffname.Items.Clear();

GetStaffList();

ddlstaffname.SelectedValue = StaffId;

}

catch (Exception ex)

{

this.lblerror.Text = ex.Message;

}

svhanda Old Hand Points: 309 More actions · Answer 6

steveb (4/30/2008)
so EMP_ID =100 needs to have different values for Comp_Id,
4567, 9876 ?
do the tables have a primary key?

Hi steve...

Thanks for reply..

no table don't have any primary key..

Q -631159 Ten Centuries Points: 1077 More actions · Answer 7

If you want to avoid issues in the future you could ad a primary key field tha is an int and make it an identity field. This will give you a column that will individual values for each row. If you do not want a primary key, and you can guarantee that the rows will never be identical, you could always update the row based on the combination of the two columns

Update table1

set column2 = value

where column1 = valuecol1

and column2 = valuecol2

I don't recommend this but this will work. I highly recommend a primary key.

Q

Please take a number. Now serving emergency 1,203,894

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

svhanda (4/30/2008)
steveb (4/29/2008)
do you want Education column to be updated to the same value?
can you post the table structure and some matching sample data for the other table ?
Hi...
Thanks for reply,
My query is suppose there is table like
EMP_ID Comp_ID
100 1234
100 2345
here for same employee comp_id is different..now i am fetching updated value from SAP..so if COMP_ID will get changed..
say..
EMP_ID Comp_ID
100 4567
100 9876
so how should i write stored procedure so that distinct value of same EMP_ID will get updated...
Pls reply...

You need to know which row with EMPID = 100 has been updated. You are "fetching updated value from SAP", so what other columns are you fetching which will enable you to identify the correct row?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

steveb. SSC-Forever Points: 46733 More actions · Answer 9

svhanda (4/30/2008)
steveb (4/30/2008)
so EMP_ID =100 needs to have different values for Comp_Id,
4567, 9876 ?
do the tables have a primary key?
Hi steve...
Thanks for reply..
no table don't have any primary key..

If you don't have a primary key then it is going to be difficult to do this correctly.

It looks from the sample that you have posted that you could just re-create the whole table from your SAP import.

What other data do you have in your local tables that related to the EMP_ID ?

These could be remerged into your new table and add a primary key.