如果条件匹配，MYSQL将数据从一个表列移动到另一个表列

i'm not really an mysql guy more like a php guy :)

我真的不是一个更喜欢php家伙的mysql家伙:)

I have an issue with copying values from one table column to another table column.

我有一个问题，从一个表列复制值到另一个表列。

The trick is that the data should be copied only if condition is matched. So basically i want to transfer categoryID from one table to another if postIDs are the same.

诀窍是只有在条件匹配时才应复制数据。所以基本上我想将categoryID从一个表转移到另一个表，如果postID是相同的。

i have two tables news and news_in_category

我有两个表news和news_in_category

in news table i have the following columns (id, title, categoryID)

在新闻表中，我有以下列（id，title，categoryID）

in news_in_category i have the following columns (newsId, newsCategoryId)

在news_in_category中我有以下列（newsId，newsCategoryId）

So i want to move newsCategoryId to categoryID if newsId is the same as id.

因此，如果newsId与id相同，我想将newsCategoryId移动到categoryID。

For example if news table id=99 it should look up in news_in_category table find the newsId with value 99 and copy the newsCategoryId value to news table categoryID with id 99

例如，如果新闻表id = 99，它应该在news_in_category表中查找找到值为99的newsId并将newsCategoryId值复制到id为99的新闻表categoryID

Hope it makes sense to you :)

希望你有意义:)

Thank you!

谢谢！

1 个解决方案

#1

Try:

尝试：

UPDATE news n
JOIN news_in_category nic ON n.id = nic.newsId
SET n.categoryID = nic.newsCategoryId

It looks like you might have a redundant functional dependency: newsId -> categoryId. If so, you could drop the news_in_category table without any loss of information, after running the query above.

看起来你可能有一个冗余的功能依赖：newsId - > categoryId。如果是这样，您可以在运行上述查询后删除news_in_category表而不会丢失任何信息。