CODEWITHSUNDEEP
mysql
Naz
Naz

Reputation: 910

SQL INSERT INTO query syntax

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      |       |
 +--------------------+----------+-------+

Upvotes: 0

Views: 78

Answers (2)

Naktibalda
Naktibalda

Reputation: 14110

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;

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172578

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;

Upvotes: 1

Related Questions