Alias of scalar subquery

Hi,

is there a way I can rewrite this to avoid the double subquery?

UPDATE the_table AS outer_table SET
parent_id = (SELECT id FROM (SELECT * FROM the_table) AS inner_table
WHERE inner_table.number = outer_table.parent_number),
changed_flag = 1
WHERE outer_table.parent_id != (SELECT id FROM (SELECT * FROM
the_table) AS inner_table WHERE inner_table.number =
outer_table.parent_number)

Note that the two suqueries are absolutely identical.

BTW: Certainly this is a problem of clarity but is it also a problem
of performance or is the subquery cached?

Regards,
André

On Thu, 08 May 2008 19:50:06 +0200, André Hänsel <andre@webkr.de> wrote:

> Hi,
>
> is there a way I can rewrite this to avoid the double subquery?
>
> UPDATE the_table AS outer_table SET
> parent_id = (SELECT id FROM (SELECT * FROM the_table) AS inner_table
> WHERE inner_table.number = outer_table.parent_number),
> changed_flag = 1
> WHERE outer_table.parent_id != (SELECT id FROM (SELECT * FROM
> the_table) AS inner_table WHERE inner_table.number =
> outer_table.parent_number)
>
> Note that the two suqueries are absolutely identical.
>
> BTW: Certainly this is a problem of clarity but is it also a problem
> of performance or is the subquery cached?
>
> Regards,
> André

UPDATE the_table o
JOIN the_table AS i
ON o.parent_number = i.number
AND o.parent_id != i.id
SET o.parent_id = i.id,
o.changed = 1;
--
Rik Wasmus

On 8 Mai, 20:03, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Thu, 08 May 2008 19:50:06 +0200, André Hänsel <an...@webkr.de> wrote:
> > Hi,
>
> > is there a way I can rewrite this to avoid the double subquery?
>
> > UPDATE the_table AS outer_table SET
> > parent_id = (SELECT id FROM (SELECT * FROM the_table) AS inner_table
> > WHERE inner_table.number = outer_table.parent_number),
> > changed_flag = 1
> > WHERE outer_table.parent_id != (SELECT id FROM (SELECT * FROM
> > the_table) AS inner_table WHERE inner_table.number =
> > outer_table.parent_number)
>
> > Note that the two suqueries are absolutely identical.
>
> > BTW: Certainly this is a problem of clarity but is it also a problem
> > of performance or is the subquery cached?
>
> > Regards,
> > André
>
> UPDATE the_table o
> JOIN the_table AS i
> ON o.parent_number = i.number
> AND o.parent_id != i.id
> SET o.parent_id = i.id,
> o.changed = 1;

I did not yet try this but shouldn't this give me "You can't specify
target table 'the_table' for update in FROM clause" since I cannot
update and select from the same table with UPDATE ... JOIN?

André Hänsel wrote:
> On 8 Mai, 20:03, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> On Thu, 08 May 2008 19:50:06 +0200, André Hänsel <an...@webkr.de> wrote:
>>> Hi,
>>> is there a way I can rewrite this to avoid the double subquery?
>>> UPDATE the_table AS outer_table SET
>>> parent_id = (SELECT id FROM (SELECT * FROM the_table) AS inner_table
>>> WHERE inner_table.number = outer_table.parent_number),
>>> changed_flag = 1
>>> WHERE outer_table.parent_id != (SELECT id FROM (SELECT * FROM
>>> the_table) AS inner_table WHERE inner_table.number =
>>> outer_table.parent_number)
>>> Note that the two suqueries are absolutely identical.
>>> BTW: Certainly this is a problem of clarity but is it also a problem
>>> of performance or is the subquery cached?
>>> Regards,
>>> André
>> UPDATE the_table o
>> JOIN the_table AS i
>> ON o.parent_number = i.number
>> AND o.parent_id != i.id
>> SET o.parent_id = i.id,
>> o.changed = 1;
>
> I did not yet try this but shouldn't this give me "You can't specify
> target table 'the_table' for update in FROM clause" since I cannot
> update and select from the same table with UPDATE ... JOIN?

Tested in MySQL 5.0.45, it works here, I don't really feel like
installing earlier versions to check wether those work too. Just try it :).

"Currently, you cannot update a table and select from the same table in
a subquery." => there is no subquery, just a good ol' join.
--
Rik Wasmus
[SPAM]
Now looking for some smaller projects to work on to fund a bigger one
with delayed pay. If interested, mail rik at rwasmus.nl
[/SPAM]

On May 9, 3:33 pm, Rik Wasmus <luiheidsgoe...@hotmail.com> wrote:
> Tested in MySQL 5.0.45, it works here, I don't really feel like
> installing earlier versions to check wether those work too. Just try it :).

You're absolutly right, that has been working all along. Damn, I
always thought, I could not UPDATE and SELECT from the same table.