Update table with duplicate rows

Question

4.00/5 (1 vote)

See more:

Using MSSQL 2000. I have a table with duplicate rows that needs to be updated. I have new values with similar table.
Table A:
UID MD_ID Type
12 11796 30
13 11796 31
15 11796 48
18 11556 302
42 11556 305
18 11552 146

Table B:
UID MD_ID Type
22 11556 312
23 11556 315
12 11552 113
15 11796 50
18 11796 45
41 11796 48

Need to update values of Table A using Table B. I see people on internet using CTE but it's not clear to me as I am new to SQL.
Table B may or may not have same number of rows as Table A, if it has less number of values, I can leave the original value as it is and if it has more values then I can skip extra values.
UID in both tables doesn't match so cannot use UID to match value.

Posted 16-Sep-14 8:47am

raju33

Add a Solution

Comments

Wendelius 16-Sep-14 15:02pm

Can you give an example, how the rows should be updated? What's the logic you're after?

raju33 16-Sep-14 20:11pm

The rows in Table A should look like,
UID MD_ID Type
12 11796 50
13 11796 45
15 11796 48
18 11556 312
42 11556 315
18 11552 113
so basically, UID and MD_KEY stays the same but Type is updated with values in Table B

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2014-09-16T09:09:00

Solution 1

If you have a table with duplicate rows you can only update both as there's no way to distinguish them: by definition of being duplicates:

What you can do is add an identity column to the table - which does distinguish the records as it will never duplicate (even if a record is deleted). Now the records can be distinguished and accessed individually.

But - the fun part - if the records are duplicates, you need to get rid of the redundant values - or at least decide how you'll pick the one you plan on accessing. One strategy you may use is that you're only interested in the one of any group with the MAX() value of the IDENTITY field.

Posted 16-Sep-14 9:09am

W∴ Balboos, GHB

Comments

raju33 16-Sep-14 20:18pm

Technically, all the rows are unique because of UID and I cannot get rid of duplicate rows.
Consider this.
UID CarName Type
1 Altima 4-dr
2 Altima 2-dr
3 Accord 4-dr
4 Accord 2-dr

and now I have a similar table where the values are
UID CarName Type
1 Altima Sedan
2 Altima Coupe
3 Accord 4-dr_Sedan
4 Accord 2-dr_Coupe

I want to update table 1 with type of table 2. Considering 4-dr != Sedan and 2 dr != Coupe..it looks logical but it does not have any relation to put in WHERE clause.

W∴ Balboos, GHB 18-Sep-14 7:47am

I don't understand the first line of your response:
"Technically, all the rows are unique because of UID and I cannot get rid of duplicate rows."

They're either duplicates, which is how I addressed your question - or they're not duplicates.

[uid] field, to me, doesn't imply uniqueness: normally, referring to a user-ID - and since they're cars, one user can have multiple vehicles (which explained possible duplicates if they bought to vehicles of same make/model).

Another possible direction for you: if you created a new table using UNION, it will automatically strip out duplicate entries (do not use UNION ALL !)

Wendelius · Answer 2 · 2014-09-16T17:30:00

If the rows are unique based on UID and I understood your question correctly, you can use a query like:

SQL

UPDATE TableA 
SET Type = (SELECT TableB.Type
            FROM TableB
            WHERE TableB.UID = TableA.UID) -- actual matching criteria for tables?
WHERE NOT EXISTS (SELECT 1 
                  FROM TableB
                  WHERE TableB.UID = TableA.UID -- actual matching criteria for tables?
                  AND TableB.Type > TableA.Type) -- or any other proper conditions

That of course needs modification as per your requirements

george4986 · Answer 3 · 2014-09-16T17:48:00

check below code in while loop for reference.

SQL

------sample table with data
drop table #tableA
drop table #tableB
create table #tableA ([UID] bigint, MD_ID bigint, [Type] bigint)
create table #tableB ([UID] bigint, MD_ID bigint, [Type] bigint)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(12,11796,30)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(13,11796,31)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(15,11796,48)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(18,11556,302)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(42,11556,305)
INSERT INTO #tableA ([UID] , MD_ID , [Type] ) VALUES(18,11552,146)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(22,11556,312)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(23,11556,315)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(12,11552,113)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(15,11796,50)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(18,11796,45)
INSERT INTO #tableB ([UID] , MD_ID , [Type] ) VALUES(41,11796,48)
drop table #tableC


--------save into temperory table #tableC.values of md_id , new type,uid (uid default as zero) for matched rows in both tables
SELECT DISTINCT   #tableB.MD_ID,#tableB.Type,0 AS UID  INTO #tableC FROM #tableA 
INNER JOIN #tableB ON #tableA.MD_ID= #tableB.MD_ID
group by #tableB.MD_ID, #tableA.MD_ID,#tableA.Type,#tableB.Type
having count(#tableA.MD_ID) =count(#tableB.MD_ID)


DECLARE @di_MD_ID BIGINT
DECLARE @di_Type BIGINT
DECLARE @di_UID BIGINT


While (Select Count(UID) From #tableC where UID=0 ) > 0
Begin
	-------------select first row values of #tableC Where UID=0
	SELECT	TOP	1	@di_MD_ID=MD_ID,	@di_Type=type  	FROM	#tableC	WHERE	UID=0
	---------------update table A---------------------------------------
	UPDATE	TOP	(1)	#tableA	SET	type =@di_Type	WHERE	(MD_ID=@di_MD_ID	AND	Type!=@di_Type)	AND
							UID NOT IN (SELECT UID FROM #tableC WHERE MD_ID=@di_MD_ID )and type!=@di_Type
	-------------select UID of last updated						
	SELECT @di_UID=uid	FROM #tableA	WHERE MD_ID=@di_MD_ID AND type=@di_Type
	-------------Update first row values of #tableC Where UID=0
	UPDATE	#tableC	SET  UID =@di_UID	WHERE	MD_ID=@di_MD_ID and type=@di_Type and UID=0

END 
 
 SELECT * FROM #tableA

good luck ;-)

Update table with duplicate rows

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0