How to insert using mutiple values and SELECT statements?

Question

I would like to create a query to insert several values along with values that come from other tables, for example:

INSERT INTO table
(date, user_id, category_id, title, description)
VALUES
    ((STR_TO_DATE('04-01-2014', '%b-%d-%Y'), 
    (SELECT id FROM users WHERE username = 'admin' LIMIT 1),
    (SELECT id FROM categories WHERE category = 'games' LIMIT 1),
    'title here',
    'description here')

This however, gives me an error and from PHP a 500 internal server error. Help please.

Rolando Isidoro · Accepted Answer · 2014-04-01 21:49:43Z

1

INSERT INTO table (fields)
SELECT
    str_to_date(),
    users.id,
    categories.id,
    'title',
    'description' 
FROM
    users 
INNER JOIN
    categories ON users.key = categories.key
WHERE
    users.username = 'admin' AND
    categories.category = 'games'

that should do it I think...

edited Apr 1, 2014 at 21:49

Rolando Isidoro

5,1242 gold badges34 silver badges46 bronze badges

answered Apr 1, 2014 at 21:28

onedevteam.com

4,24810 gold badges44 silver badges75 bronze badges

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

2 Comments

polymorph Over a year ago

Works great only problem is date is not being inserted, any thoughts?

polymorph Over a year ago

Will do, thank you. I would vote you up but don't have enough points.

Leroy · Accepted Answer · 2014-04-01 21:30:12Z

0

I'm pretty sure you need a SELECT after VALUES if you're doing it this way. Try this and see if it works.

INSERT INTO table
(date, user_id, category_id, title, description)
VALUES
SELECT 
  STR_TO_DATE('04-01-2014', '%b-%d-%Y'), 
  (SELECT id FROM users WHERE username = 'admin' LIMIT 1),
  (SELECT id FROM categories WHERE category = 'games' LIMIT 1),
  'title here',
  'description here'

answered Apr 1, 2014 at 21:30

Leroy

5543 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to insert using mutiple values and SELECT statements?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related