On Wed, Aug 13, 2003 at 01:35:39PM +0900, Nils Valentin wrote:
> Hi Fraser,
>
> I a sorry I dont understand what you want to do. Could you show how you want
> it to be and the queries which you used so far ?
>
Sure, thanks for the reply and I'll try to be more clear. As in my
previous post, suppose you have these two tables. In order to make
clearer the kind of thing I'm trying to do, I'll rename them to
'person' and 'owns'. 'person' is a list of people, and 'owns' keeps
track of who owns what. (Multiple people are able to own the same
thing.)
> > mysql> select * from person;
> > +------+
> > | id |
> > +------+
> > | hal |
> > | ron |
> > | kip |
> > | dag |
> > | bob |
> > | max |
> > +------+
> >
> > mysql> select * from owns;
> > +-----+------+
> > | id | item |
> > +-----+------+
> > | bob | 4 |
> > | bob | 5 |
> > | max | 5 |
> > +-----+------+
The functionality I want to implement is the equivalent of saying "let
every person own item 4."
For every id in table Person, I wish to insert a row into table Owns which
contains the id from Person and a constant value, say '4'.
In pseudo-code, I could write this as
For each row in table Person {
Let this_id = Person->current_row->id
INSERT INTO Owns (id, item) VALUES ( this_id, '4');
}
One additional factor is that table Owns may already contain some rows
identical to those which are going to be inserted. However, it is a
simple matter to delete those beforehand, so I'm not worrying about
that.
If mysql had subqueries, I might start off trying something like
this:
INSERT INTO Owns (id, item) VALUES( select id from Person, '4');
Hmmm... this is looking more and more unlikely to be possible to me.
Does anyone see a way to achieve this in a single query, or at least
to avoid the need to create a temp table?
Thank you,
--Fraser
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
