JOIN and SubQuery-CodePudding

I have two tables address and actor. I have to Update the address(column in address table) of actor whose actor_id(in actor table) is 36.

Will the following queries do the same?

USING JOIN:

UPDATE `address` 
INNER JOIN actor ON actor.address_id=address.address_id 
SET `address`="677 Jazz Street"
WHERE actor.actor_id=36

USING SUBQUERY:

UPDATE address
SET address = "677 Jazz Street"
WHERE
    (SELECT address_id FROM actor
        WHERE actor_id = 36);

The thing why I am asking for is when I executed subquery, it showed 620 rows affected, where as Join query then showed only 1 matched 0 affected ( this is understood because i already updated using subquery) but why that showed 620?

CodePudding user response：

No, because you're not correlating the subquery with the main table. You need a comparison in the WHERE condition.

UPDATE address
SET address = '677 Jazz Street'
WHERE address_id = (
    SELECT address_id
    FROM actor
    WHERE actor_id = 36)