Insert...Select using multiple tables

misterdrogo · #1 (**permalink**) 02-10-04, 17:57

Folks;

I have a database containing three tables: a user table with one or more attributes (e.g., normal, admin, VAR), a product table, and a junction table that connects the two. They look like the following:

user {
userid,
userisvar,
...
}

product {
prodid,
prodname,
...
}

userprodmatrix {
userindex,
prodindex
}

Here's what I want to accomplish: I want to add a new product (either by name or id) to userprodmatrix for those users who currently have product X, where X is not the same as the new product. I have no idea how to use input...select to accomplish this (I'm very new to SQL). The correct SQL would add hundreds of rows to the userprodmatrix table.

Any help provided would be most welcome.

Thanks!

David

stacey_richards · #2 (**permalink**) 02-10-04, 21:35

I tried the following on MS SQL, which worked, but it didn't work on MySQL... maybe it would give you something to start with? Hope this helps.

Code:

create database tmp;
use tmp;
create table usr (id int, name varchar(50));
insert into usr values (1, 'sam');
insert into usr values (2, 'bob');
insert into usr values (3, 'jim');
create table prd (id int, name varchar(50));
insert into prd values (1, 'one');
insert into prd values (2, 'two');
insert into prd values (3, 'three');
insert into prd values (4, 'four');
create table usrprd (usrid int, prdid int);
insert into usrprd values (1, 1);
insert into usrprd values (1, 3);
insert into usrprd values (2, 2);
insert into usrprd values (3, 3);

insert into 
  usrprd
select
  usr.id as usrid,
  4 as prdid
from
  usr,
  usrprd
where
  usrprd.usrid = usr.id and
  usrprd.prdid = 3;

select * from usrprd;

misterdrogo · #3 (**permalink**) 02-11-04, 14:13

Actually, that did work on MySQL (v4.0.17)! My resulting usrprod table is as follows:

+-------+-------+
| usrid | prdid |
+-------+-------+
| 1 | 1 |
| 1 | 3 |
| 2 | 2 |
| 3 | 3 |
| 1 | 4 |
| 3 | 4 |
+-------+-------+

Thanks VERY much, stacey_richards.

Internet Services

Web Development

Digital Marketing

Consumer Tech