0

I have created the following table:

 CREATE TABLE `test2` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `A` varchar(30) DEFAULT NULL,
  `B` varchar(30) DEFAULT NULL,
  `C` varchar(30) DEFAULT NULL,
  `D` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8

When I insert a row like this

insert into test2 (A, B, C, D) values ('a', 'b', 'c', 'd')

The insertion is successfull, but if I try to insert a row with a different value at column A, e.g.

insert into test2 (A, B, C, D) values ('aaa', 'b', 'c', 'd')

then I get error 1136: Column count doesn't match value count at row 1. Can someone please help me understand the problem? I have read similar questions but I can not find what is wrong in this case.

The table has the following trigger:

DELIMITER $$

CREATE TRIGGER test_update
AFTER INSERT
ON test2 FOR EACH ROW
BEGIN
    IF NEW.A IN (select A from test1) THEN
        update test1 
        set test1.B = new.B,
            test1.C = new.C,
            test1.D = new.D
        where test1.A = new.A;
    ELSEIF NEW.A NOT IN (select A from test1) THEN
        INSERT INTO test1 values (new.A, new.B, new.C, new.D);
    END IF;
END$$

DELIMITER ;

The purpose of the trigger is to update the table "test1" if the value of column A already exists in a row of "test1", or to insert a new row in "test1" if the value of A doesn't exist.

CREATE TABLE `test1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `A` varchar(30) DEFAULT NULL,
  `B` varchar(30) DEFAULT NULL,
  `C` varchar(30) DEFAULT NULL,
  `D` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
JDoe
  • 27
  • 7
  • 1
    Not reprodiced. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=adbcae9151c7e00d5031da18b250005e Look does a trigger exists on this table - the error may occur in its code. – Akina Dec 15 '20 at 09:01
  • This error is report for sql like this `INSERT INTO table_name(col_name1, col_name2, col_name3) VALUES('value1','value2');` so check the comma,single quotes if there's something wrong with the format. – ElapsedSoul Dec 15 '20 at 09:01
  • @Akina yes there is a trigger, I have updated my question to include it. Thank you for taking the time to answer my question. – JDoe Dec 15 '20 at 09:45
  • Show DDL for `test1`. I think it contains more than 4 columns. If so then specify columns list in INSERT. – Akina Dec 15 '20 at 09:54
  • Looking the trigger code I see that `test1.A` is unique (must be at least). If so then remove IF/subqueries and use simple INSERT .. ODKU in a trigger. – Akina Dec 15 '20 at 09:57
  • @Akina: yes indeed I didn't specify column list of test1 in insert. Thank you very much for you observation! – JDoe Dec 15 '20 at 10:05
  • It would be a lot simpler to use insert ... on duplicate key update ... rather than a trigger. – Shadow Dec 15 '20 at 10:11
  • @Shadow: I think the trigger is necessery because I need table test1 to be updated whenever new rows are inserted in table test2. But I will use "insert ... on duplicate key update" inside the trigger as it is indeed more convient. Do you think there is a way to avoid the trigger completely? – JDoe Dec 15 '20 at 10:28
  • In your trigger, you need to change the insert code to INSERT INTO test1 (A, B, C, D) values (new.A, new.B, new.C, new.D); – nacho Dec 15 '20 at 10:52

0 Answers0