Re: Merge Statement Question

At least numcoops Is in your b subquery twice.

Thanks,
Andy

On Tue, 2 Nov 2021 at 15:36, Scott Canaan <srcdco_at_rit.edu> wrote:

> I’m trying to use the merge statement. I have used it in the past, but
> not in a long time. I’m getting an ORA-00918: column ambiguously defined
> error, but it is complaining about the first “when” (when matched then) and
> I can’t see why. Here’s the statement:
>
>
>
> merge into coopeval_owner.students a
>
> using (select z.rit_uid, z.lastname, z.firstname, z.middlename,
> z.gender, z.citizenship1, z.citizenship2,
>
> z.ethnicity, z.sterm, z.year_level, z.primary_program,
> z.registered, z.regcoop,
>
> z.dce, z.preferred_email, z.gpa, z.cum_gpa,
> z.completion_term,
>
> z.expectedgradterm, z.leavingdate, reasonleaving,
>
> z.facadvisorlastname, z.facadvisorfirstname,
> z.facadvisoruid, z.facadvisoremail, z.advisorlastname,
>
> z.advisorfirstname, z.advisoruid, z.advisoremail,
> z.ntidsupportfacadvisorlastname,
>
> z.ntidsupportfacadvisorfirstname,
> z.ntidsupportfacadvisoruid, z.ntidsupportfacadvisoremail,
>
> z.campus, z.numcoops, z.degree_checkout_status,
> z.numcoops, p.id
>
> from coopeval_owner.sis_import z,
>
> coopeval_owner.programs p
>
> where z.primary_program = p.code
>
> and z.rit_uid is not null
>
> and z.dce is not null) b
>
> on (a.rituid = b.rit_uid)
>
> when matched then update set a.lastname = b.lastname, a.firstname =
> b.firstname, a.middlename = b.middlename,
>
> a.gender = b.gender, a.citizenship1 = b.citizenship1,
> a.citizenship2 = b.citizenship2,
>
> a.ethnicity = b.ethnicity, a.currentterm =
> b.currentterm, a.currentyear = b.year_level,
>
> a.currentdce = b.dce, a.isregistered = b.registered,
> a.isregisteredcoop = b.regcoop,
>
> a.currentemail = b.preferred_email, a.currentgpa =
> b.gpa, a.currentcumgpa = b.cum_gpa,
>
> a.currentcompletionterm = b.completion_term,
> a.currentexpectedgradterm = b.expectedgradterm,
>
> a.currentleavingdate = b.leavingdate, a.reasonleaving
> = b.currentleavingreason,
>
> a.modifieddate = sysdate, a.facadvisorlastname =
> b.facadvisorlastname,
>
> a.facadvisorfirstname = b.facadvisorfirstname,
> a.facadvisorid = b.facadvisoruid,
>
> a.facadvisoremail = b.facadvisoremail,
> a.advisorlastname = b.advisorlastname, a.advisorfirstname =
> b.advisorfirstname,
>
> a.advisorid = b.advisoruid, a.advisoremail =
> b.advisoremail, a.currentprogramid = b.id,
>
> a.ntidsupportfacadvisorlastname =
> b.ntidsupportfacadvisorlastname,
>
> a.ntidsupportfacadvisorfirstname =
> b.ntidsupportfacadvisorfirstname,
>
> a.ntidsupportfacadvisorid =
> b.ntidsupportfacadvisoruid, a.ntidsupportfacadvisoremail =
> b.ntidsupportfacadvisoremail,
>
> a.campus = b.campus, a.numcoops = b.numcoops,
> a.degreecheckoutstatus = b.degree_checkout_status
>
> when not matched then insert values (b.rituid, b.lastname, b.firstname,
> b.middlename, b.gender, b.isntid, b.isaalana, b.citizenship1,
> b.citizenship2,
>
> b.ethnicity, b.currentterm, b.currentyear,
> b.currentprogramid, b.currentsecondaryprogramid, b.currentdce,
> b.currentemail,
>
> b.currentgpa, b.currentcumgpa,
> b.currentcompletionterm, b.currentexpectedgradterm, 0, 0,
>
> b.leavingdate, b.reasonleaving,
> b.currentadvisorlastname, b.currentadvisorfirstname, b.currentadvisoruid,
> b.currentadvisoremail,
>
> sysdate, sysdate, b.campus, b.facadvisorlastname,
> b.facadvisorfirstname, b.facadvisormiddlename, b.facadvisoruid,
>
> b.facadvisoremail, b.advisorlastname,
> b.advisorfirstname, b.advisorid, b.advisoremail,
> b.ntidsupportfacadvisorlastname,
>
> b.ntidsupportfacadvisorfirstname,
> b.ntidsupportfacadvisorid, b.ntidsupportfacadvisoremail, b.numcoops,
> b.degreecheckoutstatus);
>
>
>
>
>
> *Scott Canaan ‘88*
>
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>
> *CONFIDENTIALITY NOTE*: The information transmitted, including
> attachments, is intended only for the person(s) or entity to which it is
> addressed and may contain confidential and/or privileged material. Any
> review, retransmission, dissemination or other use of, or taking of any
> action in reliance upon this information by persons or entities other than
> the intended recipient is prohibited. If you received this in error, please
> contact the sender and destroy any copies of this information.
>
>
>

--
http://www.freelists.org/webpage/oracle-l