phpmyadmin Question posted in
The official documentation can be found here.

MariaDB copy rows inside table – Phpmyadmin

I have a very simple table that looks like this:

enter image description here

It’s called anni_tot and it has a single field called anni. Now, there is another table that looks like this:

enter image description here

It is called people_emp.

Now, I want to insert a new field in the table anni_tot and so my table now looks like this:

enter image description here

The task I need to accomplish is: insert in the table people_emp a copy of all those data that contain the previous year (previous to 2020 which is 2019) and update the field anni. The result should be this:

enter image description here

In red there is what I should get. Basically, when a new year is added in the table anni, I need to make a copy of all the data inside people_emp that have the year = year – 1 and update that field to the current year.

I can do this via php but it’s pretty hard to maintain. I am trying to do this using a trigger but I am stucked.

I can do this but my problem is that I do now know how to retrieve the data of the rows inside people_emp. Any idea?

DELIMITER //
CREATE TRIGGER nuovo_anno
AFTER INSERT ON anni
FOR EACH ROW
BEGIN

 INSERT INTO `people_emp` (`codice`, `nome`, `anno`, `comune`) VALUES ('0', '0', '0', '0');

END; //

DELIMITER ;
Tags:

2

Answers


  1. 0

    I think you want:

    INSERT INTO people_emp (codice, nome, anno, comune) 
    SELECT pe.codice, pe.nome, 2020, pe.comune
    FROM people_emp pe
    WHERE pe.anno = 2019;

    This logic can be included in your trigger when a new row is added into the first table.

    Login or Signup to reply.
  2. 0

    I believe that something like this would help you

    DELIMITER //
CREATE TRIGGER nuovo_anno
AFTER INSERT ON anni
FOR EACH ROW
BEGIN

 INSERT INTO `people_emp` (`codice`, `nome`, `anno`, `comune`) 
    SELECT `codice`, `nome`, new.anni, `comune`
    FROM `people_emp` where anno = new.anni -1; 

END; //

DELIMITER ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search