mysql - ER_TOO_MANY_ROWS：结果包含多于一行(ER_TOO_MANY_ROWS: Result consisted of more than one row)

Question

Hello I want to write a trigger to update the age of the clients according to the birth date and the current date, the trigger should fire whenever a new appointment for a client is inserted into the database, and so I have this code:

(您好，我想编写一个触发器以根据生日和当前日期来更新客户端的年龄，只要将新的客户端约会插入数据库中，触发器就应该触发，因此我有以下代码：)

drop table if exists client;
drop table if exists appointment;
drop trigger update_age;

create table client
(
    client_VAT varchar(255),
    client_name varchar(255),
    client_birth_date date,
    client_street varchar(255),
    client_city varchar(255),
    client_ZIP varchar(255),
    client_gender char(1),
    client_age tinyint,
    primary key(client_VAT)
);

insert into client values ('234928190', 'Drogon Targaryen', '2007-05-02', 'Castelo de S?o Jorge', 'Lisboa', '1100-129 Lisboa','M', 9);/*12*/
insert into client values ('208036709', 'Viserion Targaryen', '2007-05-02', 'Castelo de S?o Jorge', 'Lisboa', '1100-129 Lisboa','M', 9);/*12*/
insert into client values ('256362157', 'Rhaegal Targaryen', '2007-05-02', 'Castelo de S?o Jorge', 'Lisboa', '1100-129 Lisboa','M', 9);/*12*/
insert into client values ('269122966', 'Daenerys Targaryen', '1990-11-11', 'Castelo de S?o Jorge', 'Lisboa', '1100-129 Lisboa','F', 9);/*29*/
insert into client values ('278457541', 'Phoebe Buffay', '1966-02-16', 'Rua da Junqueira', 'Lisboa', '1349-007 Lisboa','F', 9);/*53*/
insert into client values ('264097947', 'Joey Tribbiani', '1968-07-29', 'Rua Luís de Cam?es', 'Lisboa', '1349-009 Lisboa','M', 9);/*51*/

create table appointment
(
    doctor_VAT varchar(255),
    date_timestamp datetime, 
    appointment_description text,
    client_VAT varchar(255),
    primary key(doctor_VAT),
    foreign key(client_VAT) references client(client_VAT)
);



create trigger update_age before insert on appointment
for each row 
begin
    declare date datetime;
    declare dob datetime;
    declare age int;

    select now() into date;

    select client_birth_date into dob
    from client;

    select datediff(dob, date) into age;

    update client
    set client_age=age;
end;

insert into appointment values ('257906673', '2019-10-23 16:30:00', 'Marca??o de consulta para extra??o de dente do siso.', '287041433');

And when I run it it gives me the error of the title: "ER_TOO_MANY_ROWS: Result consisted of more than one row", I have searched online and I think that the problem is probably in the select into part from the client returning more than one row, but I want all the rows to be updated, what should I do?

(当我运行它时，出现标题错误：“ ER_TOO_MANY_ROWS：结果包含多于一行”，我在网上搜索过，我认为问题可能出在从客户返回不止一个的部分中行，但是我想更新所有行，该怎么办？)

  ask by JoNeedsCHelp translate from so

Answer 1

Your error is because of the statement

(您的错误是由于以下陈述)

select client_birth_date into dob from client; 

you cannot stuff a result set into a variable (ie all records from client)

(您不能将结果集填充到变量中（即来自客户端的所有记录）)

You could change this to select client_birth_date into dob from client where client_vat = new.client_vat ;

(您可以将其更改为从client选择client_birth_date到dob，其中client_vat = new.client_vat;)

You should read up on the use of NEW.

(您应该阅读有关NEW的用法。)

variables here https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

(这里的变量https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html)

BUT you have another problem, the statement

(但是你还有另一个问题)

update client set client_age=age;

(更新客户端集client_age = age;)

is unqualified and will update EVERY client on every insert.

(是不合格的，并且将在每次插入时更新每个客户端。)

This statement should be

(该声明应为)

update client set client_age=age where client_vat = new.client_vat;

But most of the code is unnecessary, you only need

(但是大多数代码都是不必要的，您只需要)

update client 
set client_age=datediff(dob, now())
where client_vat = new.client_vat;