Professional Web Applications Themes

MySQL Error #1093 - MySQL

Dear all, I get an error #1093 when ever i attempt to execute an UPDATE Statement with the following scenario: CREATE DATABASE `college` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; use `college`; CREATE TABLE `student` ( `student_id` SMALLINT UNSIGNED NOT NULL, `student_name` VARCHAR(100) NOT NULL, `student_avg_score` DECIMAL(10,9) UNSIGNED, CONSTRAINT PK_student PRIMARY KEY (`student_id` ASC) )ENGINE=InnoDB DEFAULT CHT=utf8; CREATE INDEX IDX_student_1 ON `student` (`student_name` ASC); CREATE INDEX IDX_student_2 ON `student` (`student_avg_score` DESC); CREATE TABLE `subject` ( `subject_id` MEDIUMINT UNSIGNED NOT NULL, `subject_avg_score` DECIMAL(10,9) UNSIGNED, CONSTRAINT PK_subject PRIMARY KEY (`subject_id` ASC) )ENGINE=InnoDB DEFAULT CHT=utf8; CREATE INDEX IDX_subject_1 ON `subject` (`subject_avg_score` DESC); CREATE TABLE ...

  1. #1

    Default MySQL Error #1093

    Dear all,

    I get an error #1093 when ever i attempt to execute an UPDATE Statement
    with the following scenario:

    CREATE DATABASE `college` DEFAULT CHARACTER SET utf8 COLLATE
    utf8_general_ci;

    use `college`;

    CREATE TABLE `student` (

    `student_id` SMALLINT UNSIGNED NOT NULL,

    `student_name` VARCHAR(100) NOT NULL,
    `student_avg_score` DECIMAL(10,9) UNSIGNED,

    CONSTRAINT PK_student PRIMARY KEY (`student_id` ASC)

    )ENGINE=InnoDB DEFAULT CHT=utf8;



    CREATE INDEX IDX_student_1 ON `student` (`student_name` ASC);
    CREATE INDEX IDX_student_2 ON `student` (`student_avg_score` DESC);



    CREATE TABLE `subject` (

    `subject_id` MEDIUMINT UNSIGNED NOT NULL,

    `subject_avg_score` DECIMAL(10,9) UNSIGNED,

    CONSTRAINT PK_subject PRIMARY KEY (`subject_id` ASC)

    )ENGINE=InnoDB DEFAULT CHT=utf8;



    CREATE INDEX IDX_subject_1 ON `subject` (`subject_avg_score` DESC);



    CREATE TABLE `scores` (

    `student_id` SMALLINT UNSIGNED NOT NULL,

    `subject_id` MEDIUMINT UNSIGNED NOT NULL,

    `score` TINYINT UNSIGNED NOT NULL,

    CONSTRAINT PK_scores PRIMARY KEY (`student_id` ASC, `subject_id`
    ASC)

    )ENGINE=InnoDB DEFAULT CHT=utf8;



    CREATE INDEX IDX_scores_1 ON `scores` (`score` DESC);



    ALTER TABLE `scores` ADD CONSTRAINT student_scores

    FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON
    DELETE CASCADE ON UPDATE CASCADE;



    ALTER TABLE `scores` ADD CONSTRAINT subject_scores

    FOREIGN KEY (`subject_id`) REFERENCES `subject` (`subject_id`) ON
    DELETE CASCADE ON UPDATE CASCADE;

    INSERT INTO `subject` VALUES (1,NULL);

    INSERT INTO `subject` VALUES (2,NULL);

    INSERT INTO `student` VALUES (1,'Robert',NULL);
    INSERT INTO `student` VALUES (2,'Michael',NULL);
    INSERT INTO `student` VALUES (3,'Sandra',NULL);
    INSERT INTO `student` VALUES (4,'Lee',NULL);
    INSERT INTO `student` VALUES (5,'Ali',NULL);
    INSERT INTO `student` VALUES (6,'Zimic',NULL);
    INSERT INTO `student` VALUES (7,'Peter',NULL);
    INSERT INTO `student` VALUES (8,'Lim',NULL);
    INSERT INTO `student` VALUES (9,'Thomas',NULL);

    INSERT INTO `scores` VALUES (1,1,9.2);
    INSERT INTO `scores` VALUES (2,1,3);
    INSERT INTO `scores` VALUES (3,1,7.46);
    INSERT INTO `scores` VALUES (4,1,3.02);
    INSERT INTO `scores` VALUES (5,1,8.8);
    INSERT INTO `scores` VALUES (6,1,4.7205);
    INSERT INTO `scores` VALUES (7,1,6.1);
    INSERT INTO `scores` VALUES (8,1,5.5);
    INSERT INTO `scores` VALUES (9,1,1.8);
    INSERT INTO `scores` VALUES (2,2,4.5);
    INSERT INTO `scores` VALUES (4,2,9.593);
    INSERT INTO `scores` VALUES (5,2,7.23);
    INSERT INTO `scores` VALUES (8,2,8.808);
    INSERT INTO `scores` VALUES (9,2,9.7);

    SELECT sb.subject_id, AVG(sc.score) AS 'Average Score'
    FROM subject sb INNER JOIN scores sc ON sc.subject_id = sb.subject_id
    GROUP BY sc.subject_id
    ORDER BY 'Average Score' DESC;

    # OK
    # subject_id Average Score
    # 2 8.2000
    # 1 5.5556

    UPDATE subject sb1 SET sb1.subject_avg_score =
    (
    SELECT AVG(sc.score)
    FROM subject sb2 INNER JOIN scores sc ON sc.subject_id = sb2.subject_id
    GROUP BY sc.subject_id
    );

    #1093 - You can\'t specify target table 'sb1' for update in FROM clause

    UPDATE subject sb1 SET sb1.subject_avg_score =
    (
    SELECT AVG(sc.score)
    FROM subject sb2 INNER JOIN scores sc ON sc.subject_id = sb2.subject_id
    WHERE sb2.subject_id = 1
    GROUP BY sc.subject_id
    ) WHERE sb1.subject_id = 1;

    #1093 - You can\'t specify target table 'sb1' for update in FROM clause

    The average score displays normaly as illustrated but when attempted to
    set its vallue to the column `subject_avg_score`, which was left empty
    on purpose, then i always face this error!

    Any hints?

    coosa Guest

  2. #2

    Default Re: MySQL Error #1093

    coosa wrote:
     

    [snip]
     

    Not sure why you would get this, but, have you tried making sb2 a view and using
    the view name? NOT TESTED:

    create view getavg as
    SELECT AVG(sc.score) as avg_score
    FROM subject sb2 INNER JOIN scores sc ON sc.subject_id = sb2.subject_id
    WHERE sb2.subject_id = 1
    GROUP BY sc.subject_id

    UPDATE subject sb1 SET sb1.subject_avg_score =
    (
    SELECT avg_score
    FROM getavg
    ) WHERE sb1.subject_id = 1;


    Also, what version/platform are you using IIRC there are some versions that did
    not support sub-selects such as this...

    --
    Michael Austin
    Database Consultant
    Domain Registration and Linux/Windows Web Hosting Reseller
    http://www.spacelots.com
    Michael Guest

  3. #3

    Default Re: MySQL Error #1093

    "coosa" <com> wrote:

    [snip]
     

    RTFM.

    http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html

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



    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  4. #4

    Default Re: MySQL Error #1093

    my version is 5.0.24a-Debian_9-log running under Ubuntu Linux AMD64
    Desktop Edition and your trick to overcome it with a view didn't work
    as well.

    Michael Austin wrote: 
    >
    > [snip]

    >
    > Not sure why you would get this, but, have you tried making sb2 a view and using
    > the view name? NOT TESTED:
    >
    > create view getavg as
    > SELECT AVG(sc.score) as avg_score
    > FROM subject sb2 INNER JOIN scores sc ON sc.subject_id = sb2.subject_id
    > WHERE sb2.subject_id = 1
    > GROUP BY sc.subject_id
    >
    > UPDATE subject sb1 SET sb1.subject_avg_score =
    > (
    > SELECT avg_score
    > FROM getavg
    > ) WHERE sb1.subject_id = 1;
    >
    >
    > Also, what version/platform are you using IIRC there are some versions that did
    > not support sub-selects such as this...
    >
    > --
    > Michael Austin
    > Database Consultant
    > Domain Registration and Linux/Windows Web Hosting Reseller
    > http://www.spacelots.com[/ref]

    coosa Guest

  5. #5

    Default Re: MySQL Error #1093

    Axel,

    Since you say "In general .." does it it mean it can be done some how?
    if yes, then how and thanks in advance.

    Axel Schwenke wrote: 
    >
    > RTFM.
    >
    > http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html
    >
    > "In general, you cannot modify a table and select from the same table
    > in a subquery."
    >
    >
    >
    > XL
    > --
    > Axel Schwenke, Support Engineer, MySQL AB
    >
    > Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    > MySQL User Forums: http://forums.mysql.com/[/ref]

    coosa Guest

  6. #6

    Default Re: MySQL Error #1093

    "coosa" <com> wrote: [/ref]
     

    It's not me who says that. This was a citation from the manual.
    Why don't you read it? An exception from the rule is shown there.


    BTW, the cited SQL statement is invalid anyway:
     

    because the inner select must either deliver a scalar result or must be
    correlated with the outer update. I guess what you wanted to do is

    UPDATE subject SET subject_avg_score = (
    SELECT AVG(score) FROM scores WHERE scores.subject_id = subject.subject_id
    );

    to update the average score of all subjects.
    Just append WHERE subject_id = ... to constrain to a single subject.


    BTW2, why do you store the average score at all? Why not calculate it
    when needed? You're adding redundancy and denormalizing your database.


    HTH, XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

Similar Threads

  1. Replies: 0
    Last Post: January 29th, 02:03 PM
  2. mySQL error in PHP that works directly in mySQL
    By jcg in forum PHP Development
    Replies: 2
    Last Post: May 25th, 10:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139