CODEWITHSUNDEEP
mysql
bikeman868
bikeman868

Reputation: 2637

MySQL Error (1093)

I read a number of posts and replies about this error, but all the replies give specific answers to the specific question. Can someone explain what the issue is, and how to overcome it in general, so that I don't have to post every problem query here.

This is the code for my procedure

CREATE PROCEDURE `sp_FixEntityNames`(IN `importId` BIGINT UNSIGNED)
BEGIN
    UPDATE ImportedSymbols s
    JOIN ExchangeMappings m ON s.ExchangeMappingId = m.ExchangeMappingId
    SET s.EntityName = 
    (
        SELECT s1.EntityName 
        FROM ImportedSymbols s1
        JOIN ExchangeMappings m1 ON s1.ExchangeMappingId = m1.ExchangeMappingId
        WHERE
            s1.ImportId = importId
                AND
            s1.Symbol = s.Symbol
                AND
            s1.Suffix = NULL
                AND
            s1.MarketId = NULL
                AND
            s1.SecurityTypeId = 1
                AND
            m1.NamespaceId = m.NamespaceId
    )
    WHERE
        s.ImportId = importId
            AND
        (
            s.Suffix != NULL
                OR
            s.MarketId != NULL
                OR
            s.SecurityTypeId != 1
        );
END

Upvotes: 0

Views: 48

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

The general explanation is that in MySQL, you can't modify the same table which you use in the SELECT part.

you can verify al this mysql Reference doc : http://dev.mysql.com/doc/refman/5.6/en/update.html

(four line before user comment you find )

You cannot update a table and select from the same table in a subquery.

Upvotes: 2

Related Questions