Error 1093 in Mysql

Question

Follow my Mysql code:

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 execute the insert on my table materias of the following error:

you can’t specify target table for update in from clause mysql.

When I run the command separately: select sum(nota1+nota2) from Materias where RA = 201500984 he executes.

Answer 1

I don’t know if you have other problems but you have a clear mistake and two other weird things that end up causing the reported error.

The primary key is ID + RA. A problem occurs when trying to insert two lines with the same primary key. Actually didn’t even get to see it because another problem happens before.

The expression (select RA from Alunos where RA=201500984) doesn’t make any sense, after all the result of the column RA certainly will be 201500984.

That doesn’t make sense either: select sum(nota1+nota2)/2 from Materias where RA = 201500984. Will you take something you’re inserting now? It won’t work.

Something like that would work:

insert into Materias values(1, 'Alged', 6.0, 7.0, (nota1 + nota2) / 2, 0.0, 201500984);

Behold working in the Sqlfiddle. Also put on the Github for future reference.

Obviously I don’t know if it’s the intention. The second row would already have to be another RA. I hope you understand why you’re not naming the columns. This case works, but can bring maintenance complications.

Answer 2

Modify the insertion query by making a INSERT with SELECT, as follows:

insert into Materias
(select 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));

I hope I’ve helped!