While Insert Records To MySQL, get in Error Code 1054-CodePudding

I'm new to MySQL & I try to enter records to mysql table. I'm getting following error

INSERT INTO advertising.discountauthorizationrequst SET DARDateTime=cast('2003-01-13 16:50:32' as datetime), `DARPubCode`=trim('DD'), `DARPubDate`=cast('2022-05-08' as date), `DARAutUser`=trim("U0001"), `DARDeviceID`=trim('123456789ABCDEFGHIJKL987456'), `DARMessage`=trim("This Is Test Message"), `DARGranted`=("0"), `DARUser`=trim("DATAENTRYUSERNAME") Error Code: 1054. Unknown column 'DARDateTime' in 'field list'

I listed my INSERT statement below. Someone please help me to solve this issue. I'm using mysql workbench 8.0.

Columns:

    DARDateTime datetime PK 
    DARPubCode varchar(3) PK 
    DARPubDate date PK 
    DARAutUser varchar(5) 
    DARDeviceID varchar(50) 
    DARMessage varchar(100) 
    DARGranted varchar(1) 
    DARUser varchar(50) PK

Here is script

    INSERT INTO `advertising`.`discountauthorizationrequst` 
    SET 
       `DARDateTime`=cast('2003-01-13 16:50:32' as datetime), 
       `DARPubCode`=trim('DD'), 
       `DARPubDate`=cast('2022-05-08' as date), 
       `DARAutUser`=trim("U0001"),
       `DARDeviceID`=trim('123456789ABCDEFGHIJKL987456'), 
       `DARMessage`=trim("This Is Test Message"), 
       `DARGranted`=("0"), 
       `DARUser`=trim("DATAENTRYUSERNAME");

Edited.. Table Inspactor - DDL

    CREATE TABLE `discountauthorizationrequst` (
  `DARDateTime` datetime NOT NULL,
  `DARPubCode` varchar(3) NOT NULL,
  `DARPubDate` date NOT NULL,
  `DARAutUser` varchar(5) DEFAULT NULL,
  `DARDeviceID` varchar(50) DEFAULT NULL,
  `DARMessage` varchar(100) DEFAULT NULL,
  `DARGranted` varchar(1) DEFAULT NULL,
  `DARUser` varchar(50) NOT NULL,
  PRIMARY KEY (`DARDateTime`,`DARPubCode`,`DARPubDate`,`DARUser`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CodePudding user response：

You are actually confusing the SQL commands and coming up with a hybrid of them. The INSERT command most commonly is done in two ways..

insert into SomeTable 
   ( these, columns ) 
   values 
   ( oneValue, anotherValue)

insert into SomeTable( these, columns )
   select oneColumn, secondColumn
      from SomeOtherTable
      where SomeCondition

The UPDATE command is based on an EXISTING record that you want to change

Update SomeTable set
      thisColumn = SomeValue,
      anotherColumn = SomeOtherValue
   where SomeCondition

So, what you appear to be doing would be written as

INSERT INTO advertising.discountauthorizationrequst
   (  DARDateTime,
      DARPubCode,
      DARPubDate,
      DARAutUser,
      DARDeviceID,
      DARMessage,
      DARGranted, 
      DARUser
   )
   values
   (
      cast('2003-01-13 16:50:32' as datetime), 
      'DD', 
      '2022-05-08', 
      'U0001', 
      '123456789ABCDEFGHIJKL987456', 
      'This Is Test Message', 
      '0', 
      'DATAENTRYUSERNAME'
   )

Notice the readability with formatting, you can see each column that is needed followed by the explicit values (which could be parameterized during code later) are in the same ordinal context. So, if you ever needed to add a new column to the insert, easy to do with the same ordinal position in the values provided secondarily to it.

As for the 3rd column, by providing a string in YYYY-MM-DD, SQL typically auto-converts to a date format. Other fields, you dont need to explicitly TRIM() everything. If parameterized, you would pass the trimmed VALUE, when you get to that point in your development.

CodePudding user response：

Thanks for your comments & answers,

I found the mistake what I did. I created triggers for above table. After I deleted those triggers its working.

Thank you again.