Question 20790 in Mysql Question posted in
The official MySQL documentation can be found here.

If Concat insert result – Mysql

I have a problem, I am making an insert using the concat to generate multiple inserts, however I realize that a null field is affecting all the queries, since the records that have null in their field, their insert is not generated correctly , now when integrating an IF() validation in the value of the field where the problem is caused, mysql sends me an error therefore the query is not executed

enter image description here

This is my query insert

SELECT concat ( "INSERT INTO `erp_gamma`.`partner` (`n_partner_id`, `n_activo`, `c_apellido_materno`, `c_apellido_paterno`, `c_correo_electronico`, `n_dias_credito`, `c_identificador`, `c_json`, `d_limite_credito`, `c_nombre`, `n_persona_fisica`, `c_razon_social`, `c_rfc`, `n_status`, `n_corporativo_sucursal_id`, `n_partner_tipo_id`, `c_codigo_postal`, `n_numero_telefonico`, `create_date`, `update_date`, `c_cp_fiscal`, `c_regimen_fiscal`, `c_sistema_id_serie_numerico`, `n_sistema_id_numerico`, `c_id_migracion`) VALUES (NULL, '1', '",partner.contacto_nombre,"', '",partner.contacto_nombre,"', '",partner.contacto_email,"', '",partner.control_dias_credito,"', '11111', '{"id_calle_cliente_md":"",partner.contacto_calle,"","id_colonia_cliente_md":"",partner.contacto_colonia,"","id_ciudad_cliente_md":"",partner.contacto_ciudad,"","id_estado_cliente_md":"",partner.contacto_estado,"","id_cp_cliente_md":"",partner.contacto_cp,"","id_telefono2_cliente_md":"",partner.contacto_telefono2,"","id_celular_cliente_md":"",partner.contacto_telefono3,"","id_fax_cliente_md":"",partner.contacto_fax,"","id_web_cliente_md":"",partner.contacto_web,"","id_tipoprecio_cliente_md":"",partner.control_tipo_precio_default,"","id_calle_rfc_cliente_md":"",partner.factura_calle,"","id_colonia_rfc_cliente_md":"",partner.factura_colonia,"","id_ciudad_rfc_cliente_md":"",partner.factura_ciudad,"","id_estado_rfc_cliente_md":"",partner.factura_estado,""}', '",partner.control_limite_credito,"', '",partner.contacto_nombre,"', '1', '",partner.factura_nombre,"', '",partner.factura_rfc,"', '1', '122', '1', '",partner.contacto_cp,"', '",partner.contacto_telefono1,"', now(), now(), '",partner.factura_cp,"', "IF(partner.factura_regimen_fiscal IS NULL," ", partner.factura_regimen_fiscal)", 'TP1_20', '20', '",partner.sku,"' );")
FROM partner
WHERE partner.tipo_partner = 1 AND partner.status =1;

Error MySQL

I hope the inserts can be generated with the records I need

Tags:

2

Answers


  1. 0

    I post the query as it is as i never can test it

    i added a lot of spaces, but i think it is more a problem from mysql workbench, as that big concat, aren’t usually done, so the parser will get into trouble

    The misstale you made is that you have is

    IF(partner.factura_regimen_fiscal IS NULL," ", partner.factura_regimen_fiscal)

    must be in commas, as it is ntpart if the string that you are combining

    SELECT concat ( "INSERT INTO `erp_gamma`.`partner` (`n_partner_id`, `n_activo`, `c_apellido_materno`, `c_apellido_paterno`, `c_correo_electronico`, `n_dias_credito`, `c_identificador`, `c_json`, `d_limite_credito`, `c_nombre`, `n_persona_fisica`, `c_razon_social`, `c_rfc`, `n_status`, `n_corporativo_sucursal_id`, `n_partner_tipo_id`, `c_codigo_postal`, `n_numero_telefonico`, `create_date`, `update_date`, `c_cp_fiscal`, `c_regimen_fiscal`, `c_sistema_id_serie_numerico`, `n_sistema_id_numerico`, `c_id_migracion`) VALUES (NULL, '1', '"
,partner.contacto_nombre,"', '",partner.contacto_nombre,"', '",partner.contacto_email,"', '"
,partner.control_dias_credito,"', '11111', '{"id_calle_cliente_md":" "
,partner.contacto_calle,"","id_colonia_cliente_md":" "
,partner.contacto_colonia," ","id_ciudad_cliente_md":" ",partner.contacto_ciudad," ","id_estado_cliente_md":" "
,partner.contacto_estado,"","id_cp_cliente_md":" ",partner.contacto_cp," ","id_telefono2_cliente_md":" "
,partner.contacto_telefono2," ","id_celular_cliente_md":" ",partner.contacto_telefono3," ","id_fax_cliente_md":" "
,partner.contacto_fax," ","id_web_cliente_md":" ",partner.contacto_web," ","id_tipoprecio_cliente_md":" "
,partner.control_tipo_precio_default," ","id_calle_rfc_cliente_md":" ",partner.factura_calle," ","id_colonia_rfc_cliente_md":" "
,partner.factura_colonia," ","id_ciudad_rfc_cliente_md":" ",partner.factura_ciudad," ","id_estado_rfc_cliente_md":" "
,partner.factura_estado," "}', ' ",partner.control_limite_credito,"', '",partner.contacto_nombre
,"', '1', '",partner.factura_nombre,"', '",partner.factura_rfc,"', '1', '122', '1', '"
,partner.contacto_cp,"', '",partner.contacto_telefono1,"', now(), now(), '"
,partner.factura_cp,"', ",IF(partner.factura_regimen_fiscal IS NULL," ", partner.factura_regimen_fiscal),", 'TP1_20', '20', '",partner.sku,"' );")
FROM partner
WHERE partner.tipo_partner = 1 AND partner.status =1;
    Login or Signup to reply.
  2. 0

    Try using a case statement formatted something like this;

    case when partner.factura_regimen_fiscal IS NULL then "" else
    partner.factura_regimen_fiscal end

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

Back To Top
Search