MYSQL UPDATE using LEFT JOIN

Question

Question;

In the below MySQL query the inner query which produces the list of ConnectionIDs returns 34 ConnectionIDs. When I then run the two queries together as an update the query result indicated that only 14 rows were affected. Is my understanding of how the UPDATE function incorrect?

Notes;

I am trying to update my database so that the DefaultUserType in the Partners table is equal to the RoleID in the connections table for any given connection where the Connections.PartnerID is equal to the Partners.PartnerID.

Query;

    UPDATE Connections
    LEFT JOIN Partners
        ON Connections.PartnerID = Partners.PartnerID
SET Connections.RoleID = Partners.DefaultUserType
WHERE
    ConnectionID IN(
        SELECT
            sub_qu_1.ConnectionID
        FROM
            (
                SELECT DISTINCT
                    Connections.ConnectionID,
                    Connections.MemberID,
                    Connections.FriendID,
                    Connections.RoleID,
                    Partners.DefaultUserType,
                    IF(Connections.RoleID = Partners.DefaultUserType,0,1)AS not_equal
                FROM
                    Connections
                LEFT JOIN Contacts ON Connections.FriendID = Contacts.EEID
                LEFT JOIN Partners ON Contacts.PartnerID = Partners.PartnerID
                WHERE
                    Partners.DefaultUserType NOT IN (28,29,30)
            )AS sub_qu_1
        WHERE
            not_equal = 1
        AND DefaultUserType IS NOT NULL
    )

which inner query you mean? point out that in your question. After your inner query there is Where condition "Wherer not_equal = 1" . May be that is the reason — DevelopmentIsMyPassion, Apr 19 '13 at 10:19
I've updated the question. I was refering to the inner query that produces the list of ConnectionIDs (starting SELECT sub_qu_1.ConnectionID) — David.LPower, Apr 19 '13 at 10:38

Joan Vidal · Answer 1 · 2013-04-19T12:12:28.593

0

I hope you do not have millions of records! with Coalesce(val,0) you set 0 if not record in Partners

    UPDATE Connections
SET Connections.RoleID = coalesce((select Partners.DefaultUserType FROM Partners WHERE Connections.PartnerID = Partners.PartnerID LIMIT 1),0)
WHERE
ConnectionID IN(
    SELECT
        sub_qu_1.ConnectionID
    FROM
        (
            SELECT DISTINCT
                Connections.ConnectionID,
                Connections.MemberID,
                Connections.FriendID,
                Connections.RoleID,
                Partners.DefaultUserType,
                IF(Connections.RoleID = Partners.DefaultUserType,0,1)AS not_equal
            FROM
                Connections
            LEFT JOIN Contacts ON Connections.FriendID = Contacts.EEID
            LEFT JOIN Partners ON Contacts.PartnerID = Partners.PartnerID
            WHERE
                Partners.DefaultUserType NOT IN (28,29,30)
        )AS sub_qu_1
    WHERE
        not_equal = 1
    AND DefaultUserType IS NOT NULL
)

Corrected!

edited Apr 19 '13 at 12:12

answered Apr 19 '13 at 10:59

Joan Vidal

1
1

Hello Joan, unfortunately your suggested method produces incorrect results. When I examined the result of the query the RoleID in the Connections table does not match the associated Partners DefaultUserType. – David.LPower Apr 19 '13 at 11:32
Sorry: ... SET Connections.RoleID = coalesce((select Partners.DefaultUserType FROM Partners WHERE Connections.PartnerID = Partners.PartnerID LIMIT 1),0) – Joan Vidal Apr 19 '13 at 12:10
Thanks for getting back to me on this issue Joan. Unfortunately the query only affects 14 of the 34 rows that the inner query identifies. – David.LPower Apr 19 '13 at 13:04

MYSQL UPDATE using LEFT JOIN

1 Answers1