On Oct 14, 4:23 pm, JNewMember <[EMAIL PROTECTED]> wrote:
> Hi David,
>
> I have two tables that have maintain by two groups and recently found
> out that they overlap to some extend. The column-A that I represented
> was a user name column. I cant say exactly what the column-B and
> column-D are due to information policy :( but lets say its a contact
> info or some number. But I how could we accomplish without a union..?
>
> Thanks alot.
> Anu
>
> On Oct 14, 8:46 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
>
>
>
> > On Oct 13, 11:35 pm, JNewMember <[EMAIL PROTECTED]> wrote:
>
> > > I want to do a merge over a common column, lets say i have two tables
> > > that reflect fallowing structure.
>
> > > Table 1 Table
> > > 2
> > > Column-A Column-B Column-
> > > A Column-D
> > > --------------- ---------------
> > > ----------------- ----------------
> > > 0001 233
> > > 0003 67000
> > > 0003* 3000
> > > 0006 5500
> > > 0002 80
> > > 0001 45000
> > > 0003 (not mistake) 5000
>
> > > So Column-A can have same value twice (not Unique - I know bad design-
> > > This is more of data extraction). Also table 2 has the same Column-A.
>
> > > I was thinking to do a union such as
> > > Select Column-A, Column-B from Table1 union select Column-A, Column-D
> > > from table2
>
> > > is this the bast way to do..?
>
> > > Thanks,
>
> > What, exactly, do you want as your end result? A UNION will do just
> > that, UNION the results, preserving unique rows:
>
> > SQL> create table table_1
> > 2 ( column_A varchar2(6)
> > 3 column_B number);
>
> > Table created.
>
> > SQL>
> > SQL> create table table_2
> > 2 ( column_A varchar2(6)
> > 3 column_D number);
>
> > Table created.
>
> > SQL>
> > SQL> insert all
> > 2 into table_1
> > 3 values('0001', 233)
> > 4 into table_1
> > 5 values('0003', 3000)
> > 6 into table_1
> > 7 values('0002', 80)
> > 8 into table_1
> > 9 values('0003', 5000)
> > 10 into table_2
> > 11 values('0003', 67000)
> > 12 into table_2
> > 13 values('0006', 5500)
> > 14 into table_2
> > 15 values('0001', 45000)
> > 16 select * From dual;
>
> > 7 rows created.
>
> > SQL>
> > SQL> commit;
>
> > Commit complete.
>
> > SQL>
> > SQL> select
> > 2 column_a,
> > 3 column_b
> > 4 from
> > 5 table_1
> > 6 union
> > 7 select
> > 8 column_a,
> > 9 column_d
> > 10 from table_2;
>
> > COLUMN COLUMN_B
> > ------ ----------
> > 0001 233
> > 0001 45000
> > 0002 80
> > 0003 3000
> > 0003 5000
> > 0003 67000
> > 0006 5500
>
> > 7 rows selected.
>
> > SQL>
>
> > Is this how you want these results to appear? Are you certain that
> > associating a given 'key' value with multiple results is the desired
> > output? What, exactly, do these tables contain? Why is this
> > 'process' necessary? You have much to explain before any usable
> > answer can be given.
>
> > David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
Without knowing what TYPE of data is in COLUMN B and COLUMN D it's
difficult to tell you what you need to do. On the face of it (and
that's a pretty thin premise to base anything upon) it appears you
would need a MERGE INTO statement:
SQL> select * From table_1;
COLUMN COLUMN_B
------ ----------
0001 233
0003 3000
0002 80
0003 5000
SQL>
SQL> select * from table_2;
COLUMN COLUMN_D
------ ----------
0003 67000
0006 5500
0001 45000
SQL>
SQL> merge into table_1 t1
2 using table_2 t2 on (t2.column_a = t1.column_a)
3 when matched then
4 update
5 set t1.column_b = t2.column_d
6 when not matched then
7 insert
8 (column_a, column_b)
9 values (t2.column_a, t2.column_d);
4 rows merged.
SQL>
SQL> select *
2 From table_1
3 order by 1;
COLUMN COLUMN_B
------ ----------
0001 45000
0002 80
0003 67000
0003 67000
0006 5500
SQL>
SQL> select distinct *
2 From table_1
3 order by 1;
COLUMN COLUMN_B
------ ----------
0001 45000
0002 80
0003 67000
0006 5500
SQL>
Your duplicate entries can pose a problem for your MERGE operation, as
you'll need to decide which of the duplicate 'key' records to preserve
for further processing. You might be better served by creating a
temporary table containing only the data you want procesed:
SQL> create table table_1_good
2 as select column_a, max(column_b) column_b
3 from table_1
4 group by column_a;
Table created.
SQL>
SQL> select *
2 from table_1_good;
COLUMN COLUMN_B
------ ----------
0002 80
0001 233
0003 5000
SQL>
SQL> merge into table_1_good t1
2 using table_2 t2 on (t2.column_a = t1.column_a)
3 when matched then
4 update
5 set t1.column_b = t2.column_d
6 when not matched then
7 insert
8 (column_a, column_b)
9 values (t2.column_a, t2.column_d);
3 rows merged.
SQL>
SQL> select *
2 From table_1_good
3 order by 1;
COLUMN COLUMN_B
------ ----------
0001 45000
0002 80
0003 67000
0006 5500
SQL>
Again, not knowing the nature of this data any recommendation is pure
guesswork.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
