CODEWITHSUNDEEP
mysql
LTech
LTech

Reputation: 1761

INSERT INTO and SELECT multiple values in mysql statement

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.

Upvotes: 0

Views: 125

Answers (2)

Sim1
Sim1

Reputation: 532

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

Upvotes: 1

Abhishekh Gupta
Abhishekh Gupta

Reputation: 6236

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;

Upvotes: 1

Related Questions