SQL INSERT INTO query syntax

Question

I am trying to run an MySQL query to copy over data from an old table (ps__product_review/rate) to a new table (ps_product_comment/grade) based on review ID (id_product_comment). But I am a bit lost on the SQL query, this is what I have but keep getting errors.

  INSERT INTO ps_product_comment [(grade)] 
   SELECT rate
   FROM ps__product_review
   [WHERE ps__product_review.id_product_comment=ps_product_comment.id_product_comment];

Can anyone help write the correct query?

Edit:Essentially I am trying to populate the Grade column in the new table below.

Old table (ps__product_review)

+--------------------+----------+-----+
| id_product_comment | Comment  | Rate|
+--------------------+----------+-----+
|  1                 | Good     |  2  |
|  2                 | Great    |  5  |
|  3                 | OK       |  3  |
|  4                 | Brill    |  4  |
|  5                 | OK       |  3  |
|  6                 | Average  |  2  |
|  7                 | Bad      |  1  |
+--------------------+----------+-----+


New Table (ps_product_comment)
 +--------------------+----------+-------+
 | id_product_comment | Comment  | Grade |
 +--------------------+----------+-------+
 |  1                 | Good     |       |
 |  2                 | Great    |       |
 |  3                 | OK       |       |
 |  4                 | Brill    |       |
 |  5                 | OK       |       |
 |  6                 | Average  |       |
 |  7                 | Bad      |       |
 +--------------------+----------+-------+

When you ask a question here, you should provice as much information as possible. For example what errors you get. Any way you should remove square brackets. In docs square brackets mean that this part of query is optional. — Jakub Matczak
– Jakub Matczak, Commented Aug 22, 2014 at 11:48

Naktibalda · Accepted Answer · 2014-08-22 12:37:13Z

1

If you want to update table with data from another table, use UPDATE with JOIN

UPDATE ps_product_comment 
JOIN ps__product_review
ON ps__product_review.id_product_comment = ps_product_comment.id_product_comment
SET ps_product_comment.grade = ps__product_review.rate;

answered Aug 22, 2014 at 12:37

Naktibalda

14.2k5 gold badges37 silver badges53 bronze badges

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

Comments

Rahul Tripathi · Accepted Answer · 2014-08-22 11:48:14Z

1

Remove the square brackets and I think you are missing the JOIN(since you are using that in your where clause):

   INSERT INTO ps_product_comment (grade)
   SELECT rate
   FROM ps__product_review inner join ps_product_comment on 
   ps__product_review.id_product_comment=ps_product_comment.id_product_comment;

answered Aug 22, 2014 at 11:48

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

4 Comments

Naz Over a year ago

This didn't work, it copied the Rank/Grade from the old table and pasted it at the bottom of the new table, not into the correct column/row. I have updated question to clarify what I meant.

Rahul Tripathi Over a year ago

@Naz:- You need to add the column name in the insert and select clause and you are done!

Naz Over a year ago

Just tried that but it's still adding the data to end of the table rather than filling in the blank column.

Naktibalda Over a year ago

If you want to fill columns, you have to use UPDATE.

Collectives™ on Stack Overflow

SQL INSERT INTO query syntax

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related