SQL query

[pnoeric]

Hi all - quick question about the right way to go about this task in SQL.

I have a table of user names and IDs, and a second table of permissions.

I need to get some of the user IDs from the first table (let's say, everyone whose username starts with "L") and then insert into the second table the selected user ID plus fixed permissions.

I.e. in meta-SQL ;-) it's something like

SELECT user_id FROM users WHERE username LIKE 'L%';

• now remember that record set and insert a bunch of new rows into the permissions table:

INSERT INTO permissions (uid, perm1, perm2) VALUES (user_id_from_the_first_select, 1, 1);

How can I do this with MySQL? Looks like some trick with a temporary table, or a combination INSERT... SELECT and normal INSERT statment (which doesn't exist)? Obviously I'm trying to avoid looping through and doing a manual insert for each user_id.

thanks for any help,
Eric Mueller
[e-mail]

[dstockto]

It's actually not too hard. Here's what you would need to do.

INSERT INTO permissions (uid, perm1, perm2) SELECT user_id, 1, 1 FROM users WHERE username LIKE 'L%'

Then just run it. You should get all the "L" users inserted into the permissions table with perm1 equal to 1 and perm2 equal to 1. You could change these obviously, but it's not too hard to figure out from here.

Dave

[pnoeric]

Excellent! Thanks!

best,
Eric
http://www.themepark.com

[dstockto]

No problem...

Dave