<[EMAIL PROTECTED]> wrote on 01/20/2005 08:59:15 AM:
> 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
>
Please Google for tutorials on how to JOIN tables (using the explicit JOIN
clauses, not the implicit inner joins created by comma-separated lists of
tables) and RTM (see links above). Most subqueries will easily translate
to explicit JOINs. Learning this form, now, will save you lots of
heartache later.
Original ORACLE query (re-formatted):
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'
);
To be perfectly HONEST, I have had limited exposure to the ORACLE join
syntax. If I remember correctly, the (+) is on the side of the equation
with the optional results (but I could be wrong). So I think that we
translate this clause from the subquery:
from macvm a, component b, element c
where a.vm_server = b.name (+)
and b.id = c.id (+)
and c.sxvariant = 'I3'
to read:
FROM macvm a
LEFT JOIN component b
ON a.vm_server = b.name
LEFT JOIN element c
ON b.id = c.id
AND c.sxvariant = 'I3'
HOWEVER!! Because we _need_ a field from the table element to have a
particular value. We should (for performance reasons) use INNER JOINs to
ensure that the column sxvariant always contains the value 'I3'. That
would change that portion of the subquery to read:
FROM macvm a
INNER JOIN component b
ON a.vm_server = b.name
INNER JOIN element c
ON b.id = c.id
and c.sxvariant = 'I3'
Since macvm IS THE TABLE THAT CONTAINS THE FIELD YOU WANT TO UPDATE, and
the JOINs accurately define the set of records you want to change (it
usually takes a combination of JOINs and WHERE conditions to define the
set of records to update but this time it didn't) we can use this clause
"as is" as the "target" of the update statement.
PROPOSED TRANSLATION:
UPDATE macvm a
INNER JOIN component b
ON a.vm_server = b.name
INNER JOIN element c
ON b.id = c.id
AND c.sxvariant = 'I3'
SET embedded='Y';
ALTERNATIVE TRANSLATION:
UPDATE macvm a
INNER JOIN component b
ON a.vm_server = b.name
INNER JOIN element c
ON b.id = c.id
SET embedded='Y';
WHERE c.sxvariant = 'I3';
ALTERNATIVE TRANSLATION 2:
UPDATE macvm a
LEFT JOIN component b
ON a.vm_server = b.name
LEFT JOIN element c
ON b.id = c.id
SET embedded='Y';
WHERE c.sxvariant = 'I3';
If you ever want to double-check that you are going to update the correct
set of rows, check your "update target" by rearranging your UPDATE
statement into a SELECT statement. I usually list the target columns (the
columns that get SET to some value) first, then I list the columns that
participate in the JOINS and maybe even those that participate in the
WHERE clause, too. If everything seems correct, then you know you have a
good UPDATE target. Here is how I would manually verify the first
translation:
SELECT a.embedded
, a.vm_server
, b.id
, c.sxvariant
FROM macvm a
INNER JOIN component b
ON a.vm_server = b.name
INNER JOIN element c
ON b.id = c.id
AND c.sxvariant = 'I3';
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
