Creation of Script to complete data

Question

Creation of Script to complete data

Navigation

#1 by (0 votes)

0

My problem is to generate a location report by teams having the following BD:

The problem with the report is that the equipment table saves the entry ID or movement automatically but not the location, so you would have to make a script.

The easiest thing I thought was to build an extra column in equipment that saved the location once the data was updated.

For which I would have to make a script to fill in the data of that column taking into account that the BD is already populated.

I thought something like this:

update equipo INNER JOIN ingreso
ON equipo.ing_ide = ingreso.ing_ide
INNER JOIN movimiento
ON equipo.mov_ide = movimiento.mov_ide
SET equipo.pun_ide = case
                     when equipo.mov_ide = '0' then ingreso.pun_ide 
                     when equipo.mov_ide != '0' then movimiento.pun_ide
                     else 'fruta'
                     end

Problem: that only updates me by one of the two paths, when equipo.mov_ide != '0' what error am I committing?.

or do you have a better idea in the generation of this report?

------------- EXAMPLE -------------------

CREATE TABLE IF NOT EXISTS 'ingreso' (
  'ing_ide' int(11) NOT NULL,
  'ide_equipo' varchar(11) NOT NULL,  
  'pun_ide' bigint(10) NOT NULL,
  'hard_estado' varchar(11) NOT NULL,
  PRIMARY KEY ('ing_ide')
) DEFAULT CHARSET=utf8;

INSERT INTO 'ingreso' ('ing_ide', 'ide_equipo',  'pun_ide', 'hard_estado') VALUES
(1, '784476', 730012230120, '1'),
(2, '0444712', 730012230121, '1'),
(3, '2332476', 730012230122, '1'),
(4, '44476', 730012230123, '1');

CREATE TABLE IF NOT EXISTS 'movimiento' (
  'mov_ide' int(11) NOT NULL,
  'ide_equipo' varchar(11) NOT NULL,  
  'pun_ide' bigint(10) NOT NULL,
  'hard_estado' varchar(11) NOT NULL,
  PRIMARY KEY ('mov_ide')
) DEFAULT CHARSET=utf8;

INSERT INTO 'movimiento' ('mov_ide', 'ide_equipo',  'pun_ide', 'hard_estado') VALUES
(1, '784476', 830012230120, '1'),
(2, '0444712', 830012230121, '2'),
(3, '2332476', 830012230122, '4');

CREATE TABLE IF NOT EXISTS 'equipo' (
  'eqi_ide' int(11) NOT NULL,
  'ide_equipo' varchar(11) NOT NULL,  
  'ing_ide' bigint(10) NOT NULL,
  'mov_ide' varchar(11) NOT NULL,
  'pun_ide' bigint(10) NOT NULL,
  PRIMARY KEY ('eqi_ide')
) DEFAULT CHARSET=utf8;

INSERT INTO 'equipo' ('eqi_ide', 'ide_equipo', 'ing_ide' , 'mov_ide' ,'pun_ide') VALUES
(1, '784476',  '1', '1' , '0' ),
(2, '0444712',  '2', '0' , '0'),
(3, '2332476',  '3', '0' , '0');

update equipo INNER JOIN ingreso
ON equipo.ing_ide = ingreso.ing_ide
INNER JOIN movimiento
ON equipo.mov_ide = movimiento.mov_ide
SET equipo.pun_ide = case
                     when equipo.mov_ide = '0' then ingreso.pun_ide 
                     else movimiento.pun_ide
                     end

mysql sql update

asked by Andres Felipe Diaz 08.08.2018 в 19:22

source

1 answer

Problem with API Json de Chatfuel Calculate Commissions

score 0 · Accepted Answer

Try this query, the error was in the call to the index:

                UPDATE     equipo e
                INNER JOIN ingreso  i   ON e.ide_equipo = i.ide_equipo
                INNER JOIN movimiento m ON e.ide_equipo = m.ide_equipo
                SET e.pun_ide = case 
                             when e.mov_ide = 0 then i.pun_ide 
                             when e.mov_ide != 0 then m.pun_ide
                        else 'fruta'
                        end