Search code examples
mysqldatabasedata-migration

MYSQL Move data from one table column to another table column if condition is matched

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

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.

i have two tables news and news_in_category

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

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

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

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

Hope it makes sense to you :)

Thank you!

Solution

  • 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.