CODEWITHSUNDEEP
mysqlsql
Salmon
Salmon

Reputation: 133

MySQL get an error. Error: ER_DUP_FIELDNAME: Duplicate column

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);

Upvotes: 3

Views: 5591

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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);

Upvotes: 1

Related Questions