[plug] SQL help

[Lance Duivenbode]

Unfortunately that won't work. From the MySQL manual:

Starting with MySQL 4.1, all subquery forms and operations that the SQL 
standard requires are supported, as well as a few features that are 
MySQL-specific.

With MySQL versions prior to 4.1, it was necessary to work around or 
avoid the use of subqueries. In many cases, subqueries can successfully 
be rewritten using joins and other methods. See Section 13.2.8.11, 
“Rewriting Subqueries as Joins for Earlier MySQL Versions” 
<http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html>.

The only other thing you could do is to write a wrapper function that 
checks the supplied SQL statement, breaks it up, performs the required 
SELECTs etc individually, and then uses those results to run the query 
you wanted originally. It sounds like a pain but it does work - I've 
done it myself. I can't guarantee it but I'm willing to have a look at 
home tonight to see if I've got any remanent PHP code lying around that 
does. Like I said, I can't guarantee it cos I think I might have turfed 
it at some stage :(

Lance



Steve Baker wrote:

> Benjamin Woods wrote:
>
>> I have just spoken to some people on irc and they say that mysql 3.23 
>> cannot do multitable updates. they recommended upgrading the mysql 
>> server to 4.0.X after testing and backing up server.
>>
>> I am wondering, is there a way to do this without multitable requests?
>>
>>
> Yes there is, but it involves sub-selects and with my very limited 
> mysql exposure I don't know how well mysql 3.23 handles those either. 
> You do something like:
>
> update table_a
> set column_xx = (select column_yy from table_b where table_b.column_bb 
> = table_a.column_aa)
> where column_xx is null
>
> I don't remember enough about your original question to know whether 
> this achieves exactly what you want to do, but it hopefully 
> demonstrates the general structure of what you need to do.
>
> sb
>
> _______________________________________________
> PLUG discussion list: plug at plug.org.au
> http://www.plug.org.au/mailman/listinfo/plug
> Committee e-mail: committee at plug.linux.org.au
>