Error 1093 in MySQL

1

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.

    
asked by anonymous 29.08.2016 / 03:28

2 answers

2

I do not know if there are other problems but there is a clear error 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 rows with the same primary key. In fact, I did not even see it because another problem happened before.

The expression (select RA from Alunos where RA=201500984) makes no sense, after all the result of the RA column will certainly be 201500984 .

This also does not make sense: select sum(nota1+nota2)/2 from Materias where RA = 201500984 . Are you going to get something you're inserting now? It will not work.

Something like this would work:

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

Obviously I do not know if it's the intention. The second line would have to be another RA. I hope you understand why you are not naming the columns. This case works, but it can lead to maintenance complications.

See running SQLFiddle .

    
29.08.2016 / 04:13
0

Modify the insertion query, 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 have helped!

    
29.08.2016 / 23:26