CODEWITHSUNDEEP
mysql
Ragav
Ragav

Reputation: 35

MYSQL update a table based on another table's data

I have 2 MySQL tables like these

  1. Table main

| email | bounce | status |

  1. Table bounce

|email |

what i want is a query to update the (bounce and status column) main table with respect to the data from bounce table

this is the query i tried, update main inner join bounce on (main.email = bounce.email) set main.bounce='yes' and main.status='Inactive';

Upvotes: 1

Views: 96

Answers (2)

Ragav
Ragav

Reputation: 35

One of the column name in main table is same as the 2nd table name "bounce" , that's why update statement didn't work
update main inner join bounce on (main.email = bounce.email) set main.bounce='yes' and main.status='Inactive';
from the above query, main.bounce doesn't represents the column in main table instead it points to the second table "bounce"
You can update the table by either

  1. change the name of the second table and run the update statement or
  2. update the 2 columns separately
    update main inner join bounce on (main.email = bounce.email) set main.status='Inactive';
    update main set bounce='yes' where status='Inactive';

Upvotes: 1

Giannis
Giannis

Reputation: 452

You should try

UPDATE main m
INNER JOIN bounce b
ON m.email=b.email
SET m.bounce="yes" AND m.status="Inactive";

Upvotes: 1

Related Questions