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]