Insert results of subquery into table with a const

问题:

The outline of the tables in question are as follows:

I have a table, lets call it join, that has two columns, both foreign keys to other tables. Let's call the two columns userid and buildingid so join looks like

+--------------+
| join         |
|--------------|
|userid        |
|buildingid    |
+--------------+

I basically need to insert a bunch of rows into this table. Each user will be assigned to multiple buildings by having multiple entries in this table. So user 13 might be assigned to buildings 1, 2, and 3 by the following

13 1
13 2
13 3

I'm trying to figure out how to do this in a query if the building numbers are constant, that is, I'm assigning a group of people to the same buildings. Basically, (this is wrong) I want to do

insert into join (userid, buildingid) values ((select userid from users), 1)

Does that make sense? I've also tried using

select 1

The error I'm running into is that the subquery returns more than one result. I also attempted to create a join, basically with a static select query that was also unsuccessful.

Any thoughts?

Thanks, Chris

回答1:

Almost! When you want to insert to values of a query, don't try to put them in the values clause. insert can take a select as an argument for the values!

insert into join (userid, buildingid)
select userid, 1 from users

Also, in the spirit of learning more, you can create a table that doesn't exist by using the following syntax:

select userid, 1 as buildingid
into join
from users

That only works if the table doesn't exist, though, but it's a quick and dirty way to create table copies!