Phoenix.pm: mySQL question

[Scott Walters]

Tim,

mSQL and MySQL are both good databases for a number of things, but they
are both relatively simple compared to some large commercial ones. (On the
other hand, they compare favorably feature-wise to other large commercial
ones). 
The feature you want here is a "subquery", where the output of a query is
used directly in another query.


On Sun, 21 May 2000 Beaves at aol.com wrote:

> I would think this would be a simple solution, as it would seem an often 
> occurrence. 
> 
> I want to update a particular column, but the where clause contains columns 
> from two tables.  I have tried including the other table in the UPDATE 
> section after the first one, but I get an error in that case.
> 
> Here is what I want to do:  
> UPDATE icm_priv, icm_groups SET icm_priv.priv=5 WHERE 
> icm_priv.gid=icm_groups.gid
>     and
> icm_priv.uid=icm_groups.owner;
> 

This would look like, in Postgres (which is free and has more features
then mSQL and MySQL, but isn't as stable or fast, in my experience):

UPDATE icm_priv, icm_groups
SET    icm_priv.priv=5
WHERE  icm_priv.gid IN (
  SELECT icm_priv.gid 
  FROM   icm_priv, icm_groups
  WHERE icm_priv.gid=icm_groups.gid
  AND   icm_priv.uid=icm_groups.owner);
 
Actually, there are other ways that could be written, but thats a common
"trick".

If you dont want to switch databases (as you dont need these features most
of the time, or you cant switch, etc), you can always just do it in two
parts: select all of the ids, then formulate a query with a 'WHERE
icm_priv.uid IN ($listofids)' in it. This is twice as many steps, and
requires you to loop through all of the records from the first query, and
join them together with ,'s, but it really isn't that bad as long as you
are only trying to fake one subquery =)

Hope this helps! If I was too vague, swat me, and I'll cough up the goods.

> In english, set the priviledge to 5 for each member where that member is an 
> owner of a group.
> 
> It would seem to me to be a common occurrence, but mySQL has not accepted any 
> syntax that I've thrown at it.  Does mySQL have the capability to update a 
> record using a where clause that uses two separate tables?
> 
> I know I could do this in two steps using DBI, but that just seems like such 
> a waste.
> 

Ooops, I missed this last sentance on my first read-through. Sorry to
repeat to you what you already know.

-scott