Convert oracle script to mysql script

Question

Need help on this I am migrating the database from oracle to mysql. I am running the below query in oracle it works fine but in mysql getting

INSERT INTO xyz(rid, pid)
SELECT rid, pid
  FROM ((SELECT distinct rd, 
              (SELECT p.pid FROM abc p WHERE p.permission='jury') AS pid
          FROM xyz
         WHERE pid IN (SELECT p.pid
                         FROM abc p
                        WHERE p.permission in ('jury'))
       ));

Getting the below error

Every derived table must have its own alias [SQL State=42000, DB Errorcode=1248]

Boneist · Accepted Answer · 2015-08-21 13:01:51Z

1

I'm not a MySQL user, but googling the error shows that it's expecting the inline view to have an alias:

insert into xyz (rid, pid)
  select rid, pid
  from   ( (select distinct rd,
                            (select p.pid
                             from   abc p
                             where  p.permission = 'jury')
                              as pid
            from   xyz
            where  pid in (select p.pid
                           from   abc p
                           where  p.permission in ('jury')))) res;

However, isn't this just a really, really overly complicated way of doing:

insert into xyz (rid, pid)                         
select distinct xyz.rd,
                abc.pid
from   xyz
       inner join abc on (xyz.pid = abc.pid and abc.permission = 'jury');

?

answered Aug 21, 2015 at 13:01

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert oracle script to mysql script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related