CODEWITHSUNDEEP
mysqlsqldatabase
Tahir Aziz
Tahir Aziz

Reputation: 67

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

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?

Upvotes: 1

Views: 1036

Answers (3)

hmistry
hmistry

Reputation: 1

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

Upvotes: 0

Mihai
Mihai

Reputation: 26784

Try

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

Upvotes: 0

SyntaxLAMP
SyntaxLAMP

Reputation: 985

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

Upvotes: 2

Related Questions