Follow my code in MySQL:
create schema if not exists Faculdade;
use faculdade;
create table if not exists Alunos
(
RA integer UNSIGNED not null,
senha varchar(15) not null,
nome varchar(100) not null,
faltas integer,
PRIMARY KEY (RA)
)ENGINE = InnoDB;
create table if not exists Materias
(
id SMALLINT UNSIGNED NOT NULL,
nome varchar(50) not null,
nota1 float,
nota2 float,
medias float,
substitutiva float,
RA integer UNSIGNED not null,
primary key(id,RA)
)ENGINE = InnoDB;
ALTER TABLE 'Materias' ADD CONSTRAINT 'RA' FOREIGN KEY ( 'RA' ) REFERENCES 'Alunos' ( 'RA' ) ;
drop database faculdade;
describe Alunos;
describe Materias;
insert into Alunos values(201500983,'12345','Marcos Paes Leme',0);
insert into Alunos values(201500984,'54321','Dionizio',0);
insert into Alunos values(201500985,'1357','Paulo José',0);
insert into Alunos values(201500986,'2468','Robervau',0);
select * from Alunos;
insert into Materias values(1,'Alged',6.0,7.0,(select sum(nota1+nota2)/2 from Materias where RA = 201500984),0.0,(select RA from Alunos where RA=201500984));
insert into Materias values(1,'Alged',6.0,7.0,(select sum(nota1+nota2)/2 from Materias where RA = 201500984),0.0,(select RA from Alunos where RA=201500984));
select * from Materias;
When I try to run insert
on my table materias
it gives the following error:
You can not specify target table for mysql clause update.
When I run the command: select sum(nota1+nota2) from Materias where RA = 201500984
it executes separately.