How to Replace and Update Data From One Table to Another Table in MySQL

Question

I am using PHP, MySQL. I have two tables

1.categories

cat_id   cat_name

1        cars
2        mobile
3        computers
4        radios

2.posts

id       title     body    cat_id

1        title1    txt1    cars
2        title2    txt2    mobiles
3        title3    txt3    mobiles 
4        title4    txt4    radios

And I want to update the posts table replacing the cat_id value with categories table and want the following output

id       title     body    cat_id

1        title1    txt1    1
2        title2    txt2    2
3        title3    txt3    2
4        title4    txt4    4

Is there Any SQL Statement That Can Do this in One Go?

Answer 1

Here is an SQL query that should do the trick:

UPDATE posts JOIN categories ON posts.cat_id = categories.cat_name SET posts.cat_id = categories.cat_id

Answer 2

Try

UPDATE post p JOIN categories c ON p.cat_id=c.cat_name SET p.cat_id=c.cat_id

Answer 3

Use following query.

UPDATE posts as a JOIN categories as b ON a.cat_id = b.cat_name SET a.cat_id = b.cat_id