CODEWITHSUNDEEP
mysqlsqloracle-databasesyntaxsql-insert
giri
giri

Reputation: 27199

Convert oracle script to mysql script

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]

Upvotes: 0

Views: 219

Answers (1)

Boneist
Boneist

Reputation: 23578

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');

?

Upvotes: 1

Related Questions