Ristoph Ristoph - 15 days ago 5
SQL Question

Insert with multiple selects using WITH/AS

I have a table that looks like this:
user(id, username, email, password)

I'd like to insert into a "relationship" table:
(id, userid1, userid2)

But this isn't working:

INSERT INTO relationship
WITH id1 AS (SELECT id FROM user WHERE USERNAME = "a"),
id2 AS (SELECT id FROM user WHERE USERNAME = "b")
SELECT id1.id, id2.id from id1, id2


I'm sure there's another way to do this but this seems clean and I can't figure out why it isn't working. The error I get is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id1 AS

Answer

Assuming that relationship.id is an autonumber column, try:

insert into relationship (userid1, userid2)
select id1, id2
from (
    (select id as id1 from user where username='a') as a,
    (select id as id2 from user where username='b') as b
    );
Comments