SQL insert into select from multiple fields

Question

I have a table 'users' with a column of 'ID'. I want to select 2 different 'ID's' based on 2 different parameters and insert them both into another table known as 'jobs'.

INSERT INTO jobs (customer_id, client_id) 
SELECT id, id from users 
WHERE username = ? 
AND username = ?

Basically I want to get the ID of two different people and insert them both into the new table.

I would then bind the parameters to ?, and they would look something like 'john' and 'steve'. I have tried the code above but I know it is the wrong syntax. Any ideas would be greatly appreciated. Thanks

eggyal · Accepted Answer · 2014-03-06 20:46:50Z

2

You could use a self-join:

INSERT INTO jobs
  (customer_id, client_id)
SELECT customer.id, client.id
FROM   users customer
  JOIN users client ON customer.username = ? AND client.username = ?

Or, you could use subqueries:

INSERT INTO jobs
  (customer_id, client_id)
VALUES (
  (SELECT id FROM users WHERE username = ?),
  (SELECT id FROM users WHERE username = ?)
)

answered Mar 6, 2014 at 20:46

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wazzaday Over a year ago

I never thought of joining a table to itself, thank you for the accurate and very quick response!

D. Melo · Accepted Answer · 2014-03-06 20:49:19Z

0

Well, you could use subquerys

INSERT INTO jobs (customer_id, client_id) 
VALUES ( 
         (SELECT field from table where id = 2), 
         (SELECT field from table2 where id = 12) 
 )

I'm not pretty sure about the sintax, since I haven't tested, but I'm guessing it could solve it.

I often use such things in WHERE or in the SELECT statement. Just remember to return a single field and a single row in the subquerys.

answered Mar 6, 2014 at 20:49

D. Melo

2,2792 gold badges16 silver badges19 bronze badges

Collectives™ on Stack Overflow

SQL insert into select from multiple fields

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related