Apply an update statement, when uploading file to mysql table

Navigation
0

The following script takes a file and places it in a table, what I'm trying to add is an update statement of one of the added fields, where if the amount that (decimal mysql field (8,2)) is less than 0 , place in field num_boletos of that row inserted, a -1. Otherwise put a 1.

But I'm not sure where to place that sentence, or if it's better to handle it with an if (statement, true, false).

The idea is that when inserting the file, evaluate the field update num_boletos: put -1 if the field amount is less than 0. Otherwise place 1. 

load data local infile 'c:\ventas\vtascopia.txt' 
into table ventas306 
fields terminated by '¶' 
lines terminated by 'Þ' (
    clave_area,
    area_vta,
    @fecha_contable,
    sesion,
    @fecha_apertura,
    hora_apertura,av_p, 
    transaccion,
    operacion, 
    id_vta,
    tipo,
    origen,
    destino,
    @fecha_salida,
    hra_salida,
    f_boleto,
    asiento,
    pasajero,
    campo19,
    f_pago,
    ef,
    tb,pu,
    tr,
    doo,
    gp,
    cv,
    importe,
    formato_salto,
    @fecha_venta,
    hra_venta,
    corrida,
    empresa_corrida,
    t_boleto, 
    referencia,
    autorizacion,
    voucher,
    t_servicio,
    @fecha_cierre,
    razon_social,
    rfc,
    documento,
    empresa,
    fin,
    num_boletos) 

SET 

id = null,
fecha_contable = STR_TO_DATE(@fecha_contable, "%d/%m/%Y"),
fecha_apertura = STR_TO_DATE(@fecha_apertura, "%d/%m/%Y"),
fecha_salida = STR_TO_DATE(@fecha_salida, "%d/%m/%Y"),
fecha_venta = STR_TO_DATE(@fecha_venta, "%d/%m/%Y"),
fecha_cierre = STR_TO_DATE(@fecha_cierre, "%d/%m/%Y")

    --- script update ---
    UPDATE ventas306
    set num_boletos 
    where importe < 0
    
asked by Armando Arellano 08.03.2018 в 21:05
source

1 answer

1

In the update you need to add a CASE. 

update ventas306
    set num_boletos = case
    when importe < 0 then -1
    else 1
    end;

For more details, check the MySQL reference at: link

    
answered by 09.03.2018 / 16:22
source
Return variable from jsp to another jsp Convert varchar value to tinyint?