Hello,
I have two tables in an 11gR2 database which contain similiar data. There is no FK relationship between the two tables and for now I need the data in MASTER to match up to what is in SECONDARY. The table datatypes are the same with the same length.
Table 1 is called MASTER and columns are:
MTID
ADDRESS1
ADDRESS2
CITY
Table 2 is called SECONDARY and columns are:
SDID
ADDRESS1
ADDRESS2
CITY
MID and SID are the same on both tables but the rest of the columns may be the same or in some cases are different.
I ran the following query which lets me know there are 20k odd rows which have different data in these rows (I'm not at work at the mo so don't have the exact query I ran or table column names but it's close enough).
select
m.address1 as maddress1,
s.address1 as saddress1,
m.address2 as maddress2,
s.address2 as saddress2,
m.city as mcity,
s.city as scity
from master m, secondary s
where m.mtid = s.sdid
and m.address1 != s.address1
or m.address2 != s.address2
or m.city != s.city
/
I need to update the rows in master from secondary where these values are different. I've tried a couple of different update queries with no luck.
I want to do some thing like
update m.address1
set m.address1=s.address1
where m.address1 != s.address1
and m.mtid = s.stid;
But I want to update the address1, address2 and city columns in master.
Any ideas on how I can do this? After running the update the results from my select query should return zero rows.
Thanks in advance and I hope the question made sense.