CODEWITHSUNDEEP
mysqlsql
Rizwan Saleem
Rizwan Saleem

Reputation: 896

One table update accoriding to another table MySQL update error

I have two tables name activties and post_media now I want to update the media background color in activities table according to post media table record but when I run query it give me error.

Query

UPDATE A
SET A.bg_color = M.bg_color
FROM activities A
INNER JOIN post_media M ON A.relation_id = M.user_post_id AND A.media=M.file
WHERE A.relation_id>0

Error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM activities A INNER JOIN post_media M ON A.relation_id = M.user_post_' at line 3

Upvotes: 0

Views: 40

Answers (2)

Sunny Patel
Sunny Patel

Reputation: 59

Update query with use of join is different than SELECT query. Here you need to add tables before SET clause and all conditions in WHERE clause like SELECT.

e.g/ UPDATE t1, t2 SET t1.field = t2.field WHERE condition 1 AND condition 2

So your query will be like as below:

UPDATE activities A, post_media M
SET A.bg_color = M.bg_color
WHERE A.relation_id = M.user_post_id 
AND A.media=M.file
AND A.relation_id>0

Try this one.

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

UPDATE syntax is different from SELECT. There is no FROM clause usage in UPDATE statement.

General flow is: UPDATE <table name> [JOIN <other tables>] SET ...

UPDATE activities A
INNER JOIN post_media M ON A.relation_id = M.user_post_id AND A.media=M.file
SET A.bg_color = M.bg_color
WHERE A.relation_id>0

Check documentation here for full syntax and further understanding: https://dev.mysql.com/doc/refman/8.0/en/update.html

Upvotes: 4

Related Questions