INSERT INTO and SELECT multiple values in mysql statement

Question

I can't get the mysql syntax correct. I would like to insert into a table 2 pieces of data, post_id and meta_value from another table on the same database and todays date, CURDATE()) .

INSERT into wp_top_voted

(post_id, number_votes, todays_date)

The following:

SELECT `post_id`  
FROM  `wp_postmeta` WHERE  `meta_key` =  'votes' ORDER BY  `meta_value` DESC   
LIMIT 10
SELECT `meta_value`  
FROM  `wp_postmeta` WHERE  `meta_key` =  'votes' ORDER BY  `meta_value` DESC   
LIMIT 10
CURDATE())

How do I combine the above into one statment? I tried

INSERT into wp_top_voted  
(`post_id`, `number_votes`, `todays_date`)  
values (SELECT `post_id`  
FROM  `wp_postmeta` WHERE  `meta_key` =  'votes' ORDER BY  `meta_value` DESC   
LIMIT 10, SELECT `meta_value`  
FROM  `wp_postmeta` WHERE  `meta_key` =  'votes' ORDER BY  `meta_value` DESC   
LIMIT 10,   CURDATE())

but I'm getting errors.

beside the solution suggested by me , you may also want to check for the ' character you used, they seems different to me — Sim1
– Sim1, Commented Mar 16, 2015 at 10:47
I think when you insert multiple rows into table VALUES part of statement inside the parentheses also need do be separated by parentheses INSERT INTO(colum1, colum2) VALUES((val1, val2, val3), (val4, val5, val6)) etc... in your case instead of val1, val2, val3 you have SELECT statement like VALUES((SELECT statement1), (SELECT statemet 2)).. — Aleksandar Miladinovic
– Aleksandar Miladinovic, Commented Mar 16, 2015 at 11:25

Abhishekh Gupta · Accepted Answer · 2015-03-16 10:42:55Z

1

You are getting error because of incorrect format of INSERT INTO TABLE() SELECT. Try this:

INSERT into wp_top_voted  
(`post_id`, `number_votes`, `todays_date`)  
SELECT `post_id`, `meta_value`, CURDATE() 
FROM  `wp_postmeta` WHERE  `meta_key` =  'votes' ORDER BY  `meta_value` DESC   
LIMIT 10;

answered Mar 16, 2015 at 10:42

Abhishekh Gupta

6,2464 gold badges20 silver badges49 bronze badges

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

Comments

Sim1 · Accepted Answer · 2015-03-16 10:45:52Z

1

You can find a pretty good explanation here http://www.w3schools.com/sql/sql_insert_into_select.asp

You need a INSERT INTO SELECT statement

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;

Your specific case should be something like:

INSERT into wp_top_voted ('post_id','meta_value','todays_date' ) SELECT 'post_id' ,'meta_value' ,CURDATE()
FROM  'wp_postmeta' WHERE  'meta_key' =  'votes' ORDER BY 'meta_value' DESC   
LIMIT 10

edited Mar 16, 2015 at 10:45

answered Mar 16, 2015 at 10:40

Sim1

5324 silver badges24 bronze badges

Collectives™ on Stack Overflow

INSERT INTO and SELECT multiple values in mysql statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related