Re: Merge Statement
Date: Thu, 19 Mar 2009 10:41:42 -0700 (PDT)
Message-ID: <d5d06cd5-78c0-4035-a75f-84ecff91d9f9_at_h5g2000yqh.googlegroups.com>
From: ddf <oratune_at_msn.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Merge Statement
Date: Thu, 19 Mar 2009 10:41:42 -0700 (PDT)
Organization: http://groups.google.com
Lines: 42
Message-ID: <d5d06cd5-78c0-4035-a75f-84ecff91d9f9_at_h5g2000yqh.googlegroups.com>
References: <fd95a6af-0a33-4774-a1bb-8f1d69c6e951_at_j8g2000yql.googlegroups.com>
NNTP-Posting-Host: 138.32.32.166
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1237484502 1208 127.0.0.1 (19 Mar 2009 17:41:42 GMT)
X-Complaints-To: groups-abuse_at_google.com
NNTP-Posting-Date: Thu, 19 Mar 2009 17:41:42 +0000 (UTC)
Complaints-To: groups-abuse_at_google.com
Injection-Info: h5g2000yqh.googlegroups.com; posting-host8.32.32.166;
posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1;
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; Embedded Web Browser from: http://bsalsa.com/; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729),gzip(gfe),gzip(gfe)Xref: textnews.cambrium.nl comp.databases.oracle.server:84416
On Mar 19, 11:05 am, John Schaeffer <ame..._at_iwc.net> wrote:
> Merge statement is useful, but can it do anything in this situation:
>
> I have the following:
>
> Product Table: PRODUCT_CODE, PRODUCT_DESCRIPTION.
> Lookup Table: PRODUCT_ID, PRODUCT_CODE
> Updates: PRODUCT_ID, NEW_DESCRIPTION
>
> I need to update or insert the Product Table. But, in order to use
> the updates coming in, I need the Lookup Table to translate the ID
> into the Code.
>
> I know there are hundreds of ways to do this, but can MERGE do this?
Why not? Use an inline view that translates the values then match on those:
SQL> merge into bonus b
2 using (select ename, job, sal, nvl(comm,0) comm, dname
3 from emp, dept where dept.deptno = emp.deptno 4 and sal > 2000 5 and mgr is not null) d
6 on (b.ename = d.ename)
7 when matched then
8 update 9 set b.dname = d.dname 10 when not matched then 11 insert 12 (ename, job, sal, comm, dname) 13 values
14* (d.ename, d.job,d.sal, d.comm, d.dname) SQL> / 5 rows merged.
SQL> I honestly don't see the problem.
David Fitzjarrell Received on Thu Mar 19 2009 - 12:41:42 CDT