Update table with duplicate rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update table with duplicate rows

Author

Topic

raju33
Starting Member

1 Post

Posted - 2014-09-17 : 05:38:18

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.
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.

Result:
UID MD_ID Type
12 11796 50
13 11796 45
15 11796 48
18 11556 312
42 11556 315
18 11552 113

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-09-17 : 07:07:04

[CODE]
CREATE TABLE TableA (UID int, MD_ID int, Type int)
insert TableA
SELECT 12, 11796, 30 union all
SELECT 13, 11796, 31 union all
SELECT 15, 11796, 48 union all
SELECT 18, 11556, 302 union all
SELECT 42, 11556, 305 union all
SELECT 18, 11552, 146

CREATE TABLE TableB (UID int, MD_ID int, Type int)
insert TableB
SELECT 22, 11556, 312 union all
SELECT 23, 11556, 315 union all
SELECT 12, 11552, 113 union all
SELECT 15, 11796, 50 union all
SELECT 18, 11796, 45 union all
SELECT 41, 11796,48

UPDATE Ta
SET Ta.Type= Tb.Type
FROM (
SELECT *
FROM (
SELECT *, (
SELECT COUNT(*)
FROM TableA AS counter
WHERE counter.MD_ID = MyTable.MD_ID
AND counter.UID <= MyTable.UID
) AS rowNumber
FROM TableA MyTable
) AS r1) Ta
JOIN (
SELECT *
FROM (
SELECT *, (
SELECT COUNT(*)
FROM TableB AS counter
WHERE counter.MD_ID = MyTable.MD_ID
AND counter.UID <= MyTable.UID
) AS rowNumber
FROM TableB MyTable
) AS r1 ) Tb
ON Ta.MD_ID = Tb.MD_ID and Ta.rowNumber = tb.rowNumber

SELECT * FROM TableA[/CODE]

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources