Hi,
Thank you. Could you please help me in writing an equvivalent
query in mysql for the following oracle subquery?.
update macvm set embedded='Y' where vm_server in
(select a.vm_server from macvm a, component b, element c where
a.vm_server = b.name (+)
and b.id = c.id (+) and c.sxvariant = 'I3');
Thanks,
Narasimha
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 08, 2004 9:28 PM
To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS)
Cc: mysql@lists.mysql.com
Subject: Re: SubQueries
Since 4.0.22 does NOT have subqueries, you will have to use a JOIN ....
http://dev.mysql.com/doc/mysql/en/JOIN.html
http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html
... as in this example....
SELECT PAGE_SERVICE.TIMEOUT
, PAGE_SERVICE.PAGE_SERVICE_COMMENT
, PAGE_SERVICE.NUMERICMSGMAXSIZE
, PAGE_SERVICE.ALPHAMSGMAXSIZE
, PAGE_SERVICE.PASSWORD
, PAGE_SERVICE.PHONE_NO
, PAGE_SERVICE.NAME
, PAGE_SERVICE.PAGE_SERVICE_ID
FROM PAGE_SERVICE
LEFT JOIN PAGER
ON PAGE_SERVICE.PAGE_SERVICE_ID = PAGER.PAGE_SERVICE_ID
WHERE PAGER.PAGE_SERVICE_ID IS NULL;
... best wishes!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
<[EMAIL PROTECTED]> wrote on 12/08/2004 09:36:09 AM:
>
> Hi,
>
> I have a sub query in oracle, I want to convert it into Mysql
> 4.0.21 compatible
>
>
>
>
> SELECT PAGE_SERVICE.TIMEOUT, PAGE_SERVICE.PAGE_SERVICE_COMMENT,
>
>
> PAGE_SERVICE.NUMERICMSGMAXSIZE, PAGE_SERVICE.ALPHAMSGMAXSIZE,
> PAGE_SERVICE.PASSWORD, PAGE_SERVICE.PHONE_NO, PAGE_SERVICE.NAME,
> PAGE_SERVICE.PAGE_SERVICE_ID
>
> FROM PAGE_SERVICE
>
> WHERE NOT EXISTS (SELECT 1 FROM PAGER WHERE
> PAGE_SERVICE.PAGE_SERVICE_ID = PAGER.PAGE_SERVICE_ID)
>
> WITH CHECK OPTION;
>
>
>
>
> The above query is not displaying all the records whose
> PAGER.PAGE_SERVICE_ID is equal to PAGE_SERVICE.PAGE_SERVICE_ID.
>
>
>
>
> As subqueries are not supporting in mysql, could any one of you
> please give a solution .
>
>
>
>
> Waiting for the reply. Thanks in Advance
>
>
>
>
> Thanks,
>
> Narasimha
>
>
>
>
>
>
>
>
> Confidentiality Notice
>
>
> The information contained in this electronic message and any
> attachments to this message are intended
> for the exclusive use of the addressee(s) and may contain
> confidential or privileged information. If
> you are not the intended recipient, please notify the sender at
> Wipro or [EMAIL PROTECTED] immediately
> and destroy all copies of this message and any attachments.
Confidentiality Notice
The information contained in this electronic message and any attachments to
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL
PROTECTED] immediately
and destroy all copies of this message and any attachments.
