Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6272

SQL Error on perfectly valid query

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Not a Bug
        • Affects Version/s: None
        • Fix Version/s: None
        • Component/s: None
        • Labels:
          None
        • Environment:
          Windows 7

          Description

          I ran the following SQL query:

          update image set image.isMain=1 where not exists (
          select * from image i2 where image.article_id=i2.article_id and image.id < i2.id)

          I receive the following error message:

          SQL Error (1093): Table 'image' is specified twice, both as a target for 'UPDATE' and as a separate source for data

          It is perfectly valid (and often necessary, as in this case) to specify a table twice in this way. This query should work and not return an error.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                Hi,

                It is an old documented MySQL limitation that has survived many versions, see for example MySQL manual for the latest MySQL GA 5.6:
                http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html:

                In general, you cannot modify a table and select from the same table in a subquery.
                ...

                Show
                elenst Elena Stepanova added a comment - Hi, It is an old documented MySQL limitation that has survived many versions, see for example MySQL manual for the latest MySQL GA 5.6: http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html: In general, you cannot modify a table and select from the same table in a subquery. ...

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    Timwi Timwi Terby
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: