MySQL Error (1093)

Question

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

Try wrapping `SELECT s1.EntityName FROM ImportedSymbols s1 ...` in a subquery and then select `EntityName` from there. — Giorgos Betsos, Dec 16 '15 at 08:19

score 2 · Accepted Answer · answered Dec 16 '15 at 08:45

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.

MySQL Error (1093)

1 Answers1