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 CHARSET=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 CHARSET=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 CHARSET=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 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

"coosa" <coosa76 (AT) gmail (DOT) com> wrote:

[snip]

RTFM.

http://dev.mysql.com/doc/refman/5.0/...trictions.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/

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:

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:

"coosa" <coosa76 (AT) gmail (DOT) com> wrote:
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/