eliminating and replacing duplicate records

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

General SQL Server Forums

New to SQL Server Programming

eliminating and replacing duplicate records

Author

Topic

s121701
Starting Member

6 Posts

Posted - 2007-02-11 : 05:49:53

I am new to sql server and I am having deficulties writing sql script to perform the following:
1) Merging data from two tables A and B
2) Eliminate duplicate present in table B (Conditions to satisfy for dublicate:If similar address is found in both tables AND class type in Table A =1
3) merge data related to dup(eliminated records) to new table.
Not sure if we can eliminate records first before merging two tables. Tables are as follow:

Table A
Fields: ID, NAME, Address, city, zip, Class type
Value:123, John, 123 Main, NY, 71690,1
Value:124, Tom, 100 State, LA, 91070,0

Table B
Field: ID, NAME, Address, city, zip, Class Type
Value:200, Tim, 123 Main, NY, 71690,0 (duplicate; satisfied both conditions and left out in final table)
Value:124, Jack, 100 State, LA, 91070,0 (same condition but second condition is not met)
Value:320,Bob, 344 coast hwy, slc, 807760,0

Final Table:
Field: ID, NAME, Address, city, zip, Class Type
Value:123, John, 123 Main, NY, 71690,1 (should also show t
Value:124, Tom, 100 State, LA, 91070,0
Value:124, Jack, 100 State, LA, 91070,0
Value:320,Bob, 344 coast hwy, slc, 807760,0

Table d:(relate to table A:showing all products that are related to table A)
table_A.ID, Products
123, Paper 1
123, paper 2

Table e:(relate to table B: showing all products that are related to table B)
table_B.ID, Products
200, Paper 3

Final Table:
ID, Product
123, Paper 1
123, Paper 2
123, Paper 3 (changing table b id to table a)

Would appreciate any help writing script to perform such transformation. Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-02-11 : 07:31:09

not sure what you really mean... but do a select first before doing the delete and insert and see if the resultset satisfies your requirement
--duplicate
select b.id,... from tableB b
join a tableA a on b.address=a.address
and a.address=1

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-11 : 08:36:48

is this what you want ?


declare @tableA table
(
	ID		int, 
	NAME		varchar(100),
	Address		varchar(50), 
	City		varchar(50), 
	Zip		varchar(10), 	
	[Class type]	int
)

insert into @tableA
select	123, 'John', '123 Main',  'NY', '71690', 1 union all
select	124, 'Tom',  '100 State', 'LA', '91070', 0


declare @tableB table
(
	ID		int, 
	NAME		varchar(100),
	Address		varchar(50), 
	City		varchar(50), 
	Zip		varchar(10), 	
	[Class type]	int
)

insert into @tableB
select	200, 'Tim',  '123 Main',      'NY',  '71690',  0 union all
select	124, 'Jack', '100 State',     'LA',  '91070',  0 union all
select	320, 'Bob',  '344 coast hwy', 'slc', '807760', 0

declare @Final table
(
	ID		int, 
	NAME		varchar(100),
	Address		varchar(50), 
	City		varchar(50), 
	Zip		varchar(10), 	
	[Class type]	int
)

insert into @Final
select	*
from	@tableA

insert into @Final
select	*
from	@tableB b
where	not exists (select * from @Final x where x.Address = b.Address and x.[Class type] = 1)

select	*
from	@Final

declare @tableD table
(
	ID		int,
	Products	varchar(10)
)

insert into @tableD
select	123, 'Paper 1' union all
select  123, 'Paper 2'

declare @tableE table
(
	ID		int,
	Products	varchar(10)
)

insert into @tableE
select	200, 'Paper 3'

declare @tableF table
(
	ID		int,
	Products	varchar(10)
)

insert into @tableF
select	d.*
from	@tableD d inner join @Final f
	on	d.ID	= f.ID

insert into @tableF
select	e.*
from	@tableE e inner join @tableB b
	on	e.ID	= b.ID

select * from @tableF

s121701
Starting Member

6 Posts

Posted - 2007-02-11 : 22:54:53

Hi, Thanks for your reply. I am still not sure on how to get the result i m looking for. detail are as follow:
I need to write script for final table based on the original table,
I need some help in merging two tables with some conditions.
Original tables are as follow:
Table A
ID, NAME, Address, city, zip, Class type ,Status
1, ABC, 123 Main, NY, 71690,1 ,Active
2, XYZ, 100 State, LA, 91070,0,Active

Table B
ID, NAME, Address, city, zip, Class Type
3, DEF, 123 Main, NY, 71690,0,Active
4, LMN, 100 State, LA, 91070,0,Active

In my final table I would like to change status of records in Table B to inactive if the record is duplicate.Following condition should met for duplicate record:
Condition 1 = address is same in both tables
Condition 2 = Table A.ClassType = 1

Final Table:
Field: ID, NAME, Address, city, zip, Class Typ
1, ABC, 123 Main, NY, 71690,1 ,Active
2, XYZ, 100 State, LA, 91070,0,Active
3, DEF, 100 State, LA, 91070,0,InActive(this is dup, both condition met)
4,LMN, 344 coast hwy, slc, 807760,0,Active

I would like to merge related data of dup record as part of the record in table A. For example:
My original table is like:
Table C:(relate to table A anf B)
ID, Products
1, Paper 1
2, paper 2
3, Paper 3

Final Table:
ID, Product
1, Paper 1
2, Paper 2
1, Paper 3(original id was 4)

Would appreciate any help!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-11 : 23:41:32

Change of requirement ?

Please ensure the sample data posted is match with the result

quote:

Table B
ID, NAME, Address, city, zip, Class Type
3, DEF, 123 Main, NY, 71690,0,Active
4, LMN, 100 State, LA, 91070,0,Active

Final Table:
Field: ID, NAME, Address, city, zip, Class Typ
1, ABC, 123 Main, NY, 71690,1 ,Active
2, XYZ, 100 State, LA, 91070,0,Active
3, DEF, 100 State, LA, 91070,0,InActive(this is dup, both condition met)
4,LMN, 344 coast hwy, slc, 807760,0,Active


-- Prepare table for testing

declare @tableA table
(
	[ID]		int, 
	[NAME]		varchar(10),
	Address		varchar(10), 
	City		varchar(10), 
	Zip		varchar(10), 	
	[Class type]	int,
	Status		varchar(10)
)

declare @tableB table
(
	[ID]		int, 
	[NAME]		varchar(10),
	Address		varchar(10), 
	City		varchar(10), 
	Zip		varchar(10), 	
	[Class type]	int,
	Status		varchar(10)
)

declare @Final1 table
(
	[ID]		int, 
	[NAME]		varchar(10),
	Address		varchar(10), 
	City		varchar(10), 
	Zip		varchar(10), 	
	[Class type]	int,
	Status		varchar(10)
)

declare @tableC table
(
	[ID]		int,
	Product	varchar(10)
)

declare @Final2 table
(
	[ID]		int,
	Product	varchar(10)
)
-- Insert sample data into table for testing

insert into @tableA
select	1, 'ABC', '123 Main',  'NY', '71690', 1, 'Active' union all
select	2, 'XYZ', '100 State', 'LA', '91070', 0, 'Active'

insert into @tableB
select	2, 'DEF', '123 Main',  'NY',  '71690',  0, 'Active' union all
select	3, 'LMN', '100 State', 'LA',  '91070',  0, 'Active'

insert into @tableC
select	1, 'Paper 1' union all
select  2, 'Paper 2' union all
select  3, 'Paper 3'

-- Now to insert record into the Final Table
-- In my final table I would like to change status of records in Table B to inactive if the record is duplicate.Following condition should met for duplicate record:
-- Condition 1 = address is same in both tables
-- Condition 2 = Table A.ClassType = 1

insert into @Final1
select	[ID], [NAME], Address, City, Zip, [Class type], Status
from	@tableA
UNION ALL
select	[ID], [NAME], Address, City, Zip, [Class type], 
	Status = case 	when exists (select * from @tableA x where x.Address = b.Address and x.[Class type] = 1)
			then 'InActive'
			else 'Active'
			end
from	@tableB b
where	not exists (select * from @Final1 x where x.Address = b.Address and x.[Class type] = 1)

-- Select back from the final table
select	*
from	@Final1

/* RESULT
ID          NAME       Address    City       Zip        Class type  Status     
----------- ---------- ---------- ---------- ---------- ----------- ---------- 
1           ABC        123 Main   NY         71690      1           Active
2           XYZ        100 State  LA         91070      0           Active
2           DEF        123 Main   NY         71690      0           InActive
3           LMN        100 State  LA         91070      0           Active
*/

And can you explain this ? How did the Paper 3's ID change from 4 to 1?

Or is it sample data mismatched with result ?

quote:

I would like to merge related data of dup record as part of the record in table A. For example:
My original table is like:
Table C:(relate to table A anf B)
ID, Products
1, Paper 1
2, paper 2
3, Paper 3

Final Table:
ID, Product
1, Paper 1
2, Paper 2
1, Paper 3 (original id was 4)

s121701
Starting Member

6 Posts

Posted - 2007-02-12 : 01:33:48

Trying to eliminate duplicate so if we have 2 similar objects in both tables. new table should have only one entry for that object. similarly in related tables like final product table, all the products that belong to those duplicate object should be part of just 1 single object in that case Object id 1. so ID 1 should show original products of id 1 and original product of id 4.hope i explained it well.so in order to achieve this i m not sure if update query would work? any idea.thanks for you help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources