How can I write this query from MySQL (SQL) to Oracle (SQL)

Question

I have this SQL statement, but I do not know how to write it in Oracle (The Multiple Tables Update is not supported by Oracle):

UPDATE sscanner.sis_emp_card ec, sscanner.sis_cards c, sscanner.sis_employees e 
SET e.firstname='x', e.lastname='y', e.car_number=NULL, c.card_number='12345', e.email='[email protected]', ec.active='1' 
WHERE ec.id_card = c.id_card AND ec.id_emp=e.id_employee AND id=1;

sis_cards: id_card (PK) ..

sis_employees: id_employee (PK) ..

sis_emp_card: id(PK), id_card (FK) referencing sis_cards, id_employee(FK) referencing sis_employees

Robert Dupuy · Accepted Answer · 2015-09-25 12:42:50Z

I know the question is already answered, but some Oracle syntax for updates. The last is an update join view.

UPDATE sscanner.sis_employees e
       SET e.firstname='x', 
           e.lastname='y', 
           e.car_number=NULL,
           e.email = '[email protected]'
    WHERE EXISTS (
       SELECT *
       FROM sscanner.sis_emp_card ec, sscanner.sis_cards c
       WHERE ec.id_card = c.id_card AND ec.id_employee=e.id_employee AND ec.id=1);

    UPDATE sscanner.sis_cards c
        SET c.card_number = '12345'
    WHERE EXISTS(
        SELECT *
        FROM sscanner.sis_emp_card ec,  sscanner.sis_employees e
        WHERE c.id_card = ec.id_card AND ec.id_employee=e.id_employee AND ec.id=1);

    UPDATE (
        SELECT e.firstname, e.lastname, e.car_number, e.email,
        c.card_number, ec.active
        FROM sscanner.sis_emp_card ec, sscanner.sis_cards c,  sscanner.sis_employees e
        WHERE ec.id_card = c.id_card AND ec.id_employee=e.id_employee AND ec.id=1
        ) ec SET 
        ec.active='1';

mikron · Accepted Answer · 2015-09-25 12:05:21Z

Try to update one by one, like the bellow code

begin
   for R in (select e.rowid ERecId, c.rowid CRecId, ec.rowid ECRecId
               from sscanner.sis_emp_card ec, sscanner.sis_cards c,
                    sscanner.sis_employees e
              where ec.id_card = c.id_card
                and ec.id_emp = e.id_employee
                and id = 1) loop

      update sscanner.sis_employees
         set firstname = 'x',
             lastname = 'y',
             car_number = null,
             email = '[email protected]'
       where rowid = R.ERecid;

      update sscanner.sis_cards
         set card_number = '12345'
       where rowid = R.CRecId;

      update sscanner.sis_emp_card ec
         set ec.active = '1'
       where rowid = R.ECRecId;

   end loop;
end;

Collectives™ on Stack Overflow

How can I write this query from MySQL (SQL) to Oracle (SQL)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related