Re: AW: [PHP-DB] Subquery trouble :)
| From: | Maxim Maletsky | Date: | Tue, 13 May 2003 22:04:51 +0000 |
| Subject: | Re: AW: [PHP-DB] Subquery trouble :) | ||
| References: | 1 2 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-27730@lists.php.net to get a copy of this message | ||
On Tue, 13 May 2003 23:40:24 +0200
"Andreas Cahen" <qube@space.ch> wrote:
> Hi Maxim!
>
> > 3 questions:
>
> > 1. what DB is it for? Looks like Oralce to me :)
> > 2. What sense can it make updating town_id to 910 on any row that
> already contains it? 3. what is this 'Domain'? That could actually be
> your syntax error.
>
> So far... 2 questions :)
>
> 1. I use mySQL, such subqueries were working the days I used to work
> with Oracle, but those days are gone now :)
Because Oracle rocks the world and mySQL s... ...imply good :)
In short - subqueries do not work in mySQL. At least for now.
> 2. Let me explain you the structure..
>
> I got 3 tables involved...
>
> CREATE TABLE
details_domains_a (
> ID int(10) NOT NULL auto_increment,
> Domain varchar(255) NOT NULL default '',
> .
> .
> Lotsa fields :)
> .
> .
>
> town_ID int(11) NOT NULL default '0',
> PRIMARY KEY (ID)
> ) TYPE=MyISAM AUTO_INCREMENT=370 ;
>
> CREATE TABLE domains_a (
> domain varchar(255) NOT NULL default '',
> name varchar(255) NOT NULL default '',
> check int(11) NOT NULL default '0',
> check_date date NOT NULL default '0000-00-00',
> town_ID int(11) NOT NULL default '0',
> KEY domain (domain),
> KEY name (name)
> ) TYPE=MyISAM;
>
> CREATE TABLE towns (
> town_ID int(11) NOT NULL auto_increment,
> name varchar(255) NOT NULL default '',
> check int(11) NOT NULL default '0',
> check_date date NOT NULL default '0000-00-00',
> PRIMARY KEY (town_ID)
> ) TYPE=MyISAM AUTO_INCREMENT=4171 ;
>
>
>
>
>
> And I need to do following:
>
> UPDATE details_domains_a SET town_id = 910 WHERE Domain IN (
> SELECT domain
> FROM domains_a
> WHERE town_id = 910
> )
Guess what? You'll need a loop :) Not ever a stored procedure can help
you as mySQL does not support them eigher :)
So, what I would do is this:
SELECT domain
FROM domains_a
WHERE town_id = 910
then, looping the results array I would
UPDATE details_domains_a SET town_id = 910 WHERE Domain = '$domain'
Silly, isn't it? Anyone knows of a better way with mySQL?
--
Maxim Maletsky
maxim@php.net