如何在MySQL中替换和更新一个表到另一个表的数据

I am using PHP, MySQL. I have two tables

我用的是PHP, MySQL。我有两个表

1.categories

1.类

cat_id   cat_name

1        cars
2        mobile
3        computers
4        radios

2.posts

2.文章

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

我想要更新posts表，将cat_id值替换为categories，并希望得到以下输出

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?

有没有SQL语句可以一次完成这个操作?

3 个解决方案

#1

Here is an SQL query that should do the trick:

下面是一个SQL查询，它应该可以完成以下操作:

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

#2

Try

试一试

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

#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

将文章更新为a上的连接类别b。cat_id = b。cat_name设置。cat_id = b.cat_id

#1