Mysql Unknown column error on insert into

Question

I have experience an issue about sql command but it was working before, so I couldn't understand.

my table is ;

    CREATE TABLE `cdrConfCall` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `callingNumber` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
  `calledNumber` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dateTimeConnect` int(11) DEFAULT NULL,
  `dateTimeDisconnect` int(11) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `origDeviceName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
  `destDeviceName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
  `jabber` int(11) DEFAULT NULL,
  `confid` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dateTimeConnectSTR` datetime DEFAULT NULL,
  `dateTimeDisconnectSTR` datetime DEFAULT NULL,
  `causeCode` int(11) DEFAULT NULL,
  `callingName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
  `calledName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
  `catCalling` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `catCalled` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ay` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

sql command;

INSERT INTO CMOON.cdrConfCall (
                callingNumber,
                calledNumber,
                dateTimeConnect,
                dateTimeDisconnect,
                duration,
                origDeviceName,
                destDeviceName,
                jabber,
                confid,
                dateTimeConnectSTR,
                dateTimeDisconnectSTR) 
                SELECT * from
(select  '9999' as callingNumber ,'9995' as calledNumber,1625275399 as dateTimeConnect,1625275446 as dateTimeDisconnect,
'47' as duration,'xxxxx' as origDeviceName,'xxxxx' as destDeviceName,1 as jabber,'1625275399-1625275446-9999-9995' as confid,
'2021-07-03 04:23:19' as dateTimeConnectSTR,'2021-07-03 04:24:06'  as dateTimeDisconnectSTR) as tmp
                WHERE NOT EXISTS (Select * From CMOON.cdrConfCall c WHERE c.dateTimeConnect=1625275399 and c.dateTimeDisconnect=1625275446 and c.callingNumber='9999' and c.calledNumber='9995') LIMIT 1

however mysql give following error message;

Error Code: 1054. Unknown column 'dateTimeConnectSTR' in 'field list'

I don't understand issue about this query. I need help about this problem, could you help for problem detection.

I forgot write to trigger, so I edited

CREATE DEFINER=`locku`@`%` TRIGGER `CMOON`.`cdrConfCall_BEFORE_INSERT` BEFORE INSERT ON `cdrConfCall` FOR EACH ROW
BEGIN

declare calledNumberTXT varchar(95);
declare callingNumberTXT varchar(95);

declare catCalling varchar(45);
declare catCalled varchar(45);

IF new.calledName is NULL then
    
    set calledNumberTXT =    (SELECT displayname FROM `CMOON`.`cdrDevicelist` where ext=new.calledNumber order by id limit 1);
    set catCalled =  (SELECT cat FROM `CMOON`.`cdrDevicelist` where ext=new.calledNumber order by id limit 1);
    if calledNumberTXT is NULL then
        set calledNumberTXT = (select displayName from `CMOON`.`cdrLdapUser` where telephoneNumber=new.calledNumber or mobileNumber = CONCAT('+9',new.calledNumber) or mobileNumber=new.calledNumber or mail = CONCAT(new.calledNumber,'@xxx') limit 1);
        set catCalled = (select cat from `CMOON`.`cdrLdapUser` where telephoneNumber=new.calledNumber  or mobileNumber = CONCAT('+9',new.calledNumber) or mobileNumber=new.calledNumber  or mail = CONCAT(new.calledNumber,'@xxx') limit 1);
        
    end if;
    set new.calledName=calledNumberTXT;
    set new.catCalling=catCalled;
end if;

if new.callingName is NULL then
    set callingNumberTXT =    (SELECT displayname FROM `CMOON`.`cdrDevicelist` where ext=new.callingNumber order by id limit 1);
    set catCalling =  (SELECT cat FROM `CMOON`.`cdrDevicelist` where ext=new.callingNumber order by id limit 1);
    if callingNumberTXT is NULL then
        set callingNumberTXT = (select displayName from `CMOON`.`cdrLdapUser` where telephoneNumber=new.callingNumber  or mobileNumber = CONCAT('+9',new.callingNumber)  or mobileNumber=new.callingNumber or mail = CONCAT(new.callingNumber,'@xxx') limit 1);
        set catCalling =(select cat from `CMOON`.`cdrLdapUser` where telephoneNumber=new.callingNumber  or mobileNumber = CONCAT('+9',new.callingNumber) or mobileNumber=new.callingNumber or mail = CONCAT(new.callingNumber,'@xxx') limit 1);
    end if;
    set new.callingName=callingNumberTXT;
    set new.catCalled = catCalling;
    end if;
set new.ay = concat(MONTHNAME(dateTimeConnectSTR),year(dateTimeConnectSTR));
END

Please add the output of show create table CMOON.cdrConfCall maybe you are on the wrong database. — digijay
– digijay, Commented Jul 28, 2021 at 14:36
I did a test now with delete trigger and it worked. as I think this issue caused by trigger. But I don't know why it was a problem. — Orcun
– Orcun, Commented Jul 28, 2021 at 14:39

Orcun · Accepted Answer · 2021-07-28 21:09:35Z

0

I found solution this issue caused by following line in trigger ;

set new.ay = concat(MONTHNAME(dateTimeConnectSTR),year(dateTimeConnectSTR));

correct line ;

set new.ay = concat(MONTHNAME(new.dateTimeConnectSTR),year(new.dateTimeConnectSTR));

answered Jul 28, 2021 at 21:09

Orcun

834 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql Unknown column error on insert into

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related