MySQL get an error. Error: ER_DUP_FIELDNAME: Duplicate column

Question

I have a problem about MySQL, it showed this error message.

Error: ER_DUP_FIELDNAME: Duplicate column name '1'

When I using this code below.

INSERT INTO execution (employee, task, report, accept_time)
SELECT * FROM (SELECT '1', '1', '123', NOW()) AS tmp
WHERE NOT EXISTS (SELECT * FROM execution WHERE employee = '1' AND task = '1') LIMIT 1

I don't know why "(SELECT '1', '1', '123', NOW())" have duplicate problem?

This is original SQL Table.

CREATE TABLE `science_cheer`.`execution` (
  `aid` INT NOT NULL AUTO_INCREMENT,
  `employee` INT NOT NULL,
  `task` INT NOT NULL,
  `report` VARCHAR(1000) NOT NULL,
  `accept_time` DATETIME NOT NULL,
  `audit` VARCHAR(45) NOT NULL DEFAULT 'unaudited',
  PRIMARY KEY (`aid`),
  INDEX `uid_idx` (`employee` ASC),
  INDEX `tid_idx` (`task` ASC),
  CONSTRAINT `employee`
    FOREIGN KEY (`employee`)
    REFERENCES `science_cheer`.`user` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `task`
    FOREIGN KEY (`task`)
    REFERENCES `science_cheer`.`task` (`tid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

@mscdex thx help me edit, I using nodejs connect MySQL. But this question really there isn't any nodejs :) — Salmon
– Salmon, Commented Aug 17, 2014 at 13:55

Gordon Linoff · Accepted Answer · 2014-08-17 14:02:08Z

1

The problem is the name of the columns, not the values. You can give them arbitrary or meaningful names using aliases:

INSERT INTO execution(employee, task, report, accept_time)
    SELECT employee, task, report, accept_time
    FROM (SELECT '1' as employee, '1' as task, '123' as report, NOW() as accept_time
         ) AS tmp
    WHERE NOT EXISTS (SELECT * FROM execution WHERE employee = '1' AND task = '1');

The limit clause is not needed.

You can also write this as:

INSERT INTO execution(employee, task, report, accept_time)
    SELECT '1', '1', '123', now()
    FROM dual
    WHERE NOT EXISTS (SELECT * FROM execution WHERE employee = '1' AND task = '1');

However, if you want to avoid duplication in the table, you can have the database do the work. Create a unique index on execution(employee, task). You can then do an insert with on duplicate key update so it doesn't return an error when a duplicate insert is attempted:

create index idx_execution_employee_task on execution(employee, task);

And then for the insert:

insert into execution(employee, task, report, accept_time)
    select '1', '1', '123', now()
    on duplicate key update set employee = values(employee);

edited Aug 17, 2014 at 14:02

answered Aug 17, 2014 at 13:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

YuvaMac Over a year ago

It's working fine but am getting some error like this 'Deadlock found when trying to get lock; try restarting transaction' while doing this query in asyc.forEach can anyone please update me.

Collectives™ on Stack Overflow

MySQL get an error. Error: ER_DUP_FIELDNAME: Duplicate column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related