CODEWITHSUNDEEP
mysqlsqlmysql-error-1064
user1464296
user1464296

Reputation:

Operand should contain 1 column(s)

Getting a Operand should contain 1 column(s) mysql error whenever I try to insert into the table sets.

I googled and found a hoard of similar questions but they are always pin point specific to solving their immediate problem. I have mysql 5.6 by the way. I am allowed multiple TIMESTAMPS.

Here is my code:

INSERT INTO `sets` (`tabler_name`) VALUES ("leads_auto");

Here is my table:

CREATE TABLE IF NOT EXISTS `lms`.`sets` (
    `set_id` BIGINT NOT NULL AUTO_INCREMENT,
    `on_off` SMALLINT NOT NULL DEFAULT 0,
    `tabler_name` VARCHAR(45) NULL,
    `origin_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `original_count` INT NULL,
    `current_count` INT NULL,
    `source_type` VARCHAR(45) NULL,
    `source` VARCHAR(45) NULL,
    `method` VARCHAR(45) NULL,
    `agent` VARCHAR(45) NULL,
    `dupes` INT NULL,
    `bads` INT NULL,
    `aged` INT NULL COMMENT 'This table keeps track of the record sets that enter the system.        Example: a set of leads imported into the database.',
    PRIMARY KEY (`set_id`)
) ENGINE = InnoDB;

Stored Procedure:

DELIMITER //

CREATE PROCEDURE `lms`.`leads_to_bak` ()
BEGIN
SET @table1 = (SELECT `tabler_name` FROM sets WHERE on_off=0 LIMIT 1);
SET @table2 = CONCAT(@table1, '_bak');
SET @SQL1 = CONCAT('INSERT INTO ',@table2, '(', (SELECT
REPLACE(GROUP_CONCAT(COLUMN_NAME), 'lead_id,', '') FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2), ')', ' SELECT ', (SELECT                                                                   REPLACE(GROUP_CONCAT(COLUMN_NAME), 'lead_id,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE       TABLE_NAME = @table1), ' FROM ', @table1);
PREPARE stmt FROM @sql1;
EXECUTE stmt;
END//

DELIMITER ;
USE `lms`;

Trigger

DELIMITER $$

USE `lms`$$

CREATE TRIGGER `lms`.`after_insert_into_leads`
AFTER INSERT ON `sets` FOR EACH ROW
BEGIN
IF (SELECT * FROM sets WHERE on_off=0 LIMIT 1) THEN
CALL lms.leads_to_bak();
END IF;
END$$

DELIMITER ;
USE `lms`;

I don't see anything wrong with my routines. Removing the routines and trigger seems to make the problem go away.

Upvotes: 3

Views: 8899

Answers (3)

Andriy M
Andriy M

Reputation: 77667

In your trigger, did you mean to put EXISTS after IF? Like this:

CREATE TRIGGER `lms`.`after_insert_into_leads`
AFTER INSERT ON `sets` FOR EACH ROW
BEGIN
IF EXISTS (SELECT * FROM sets WHERE on_off=0 LIMIT 1) THEN
CALL lms.leads_to_bak();
END IF;
END$$

Upvotes: 3

Bjoern
Bjoern

Reputation: 16304

Besides escaping the field name in your INSERT-statement, it cannot be improved very much. But it doesn't generate any error in my test enviroment. Is this really the exact statement throwing you an error?

However, there's a slight problem in your table definition, it will throw you an

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

As the error message indicates, you can only use one timestamp column with CURRENT_TIMESTAMP, if you need more than one, you can do this using a trigger.

Upvotes: 0

Bryan
Bryan

Reputation: 6752

Escape your table name, it seems to be a reserved function. I'm not sure if you've defined one locally.

INSERT INTO `sets` (tabler_name) VALUES ("leads_auto");

Also, you can't have two timestamp fields in a single database afaik. Change one of the two timestamps to a DATETIME field if it caused you issues as well

Upvotes: 0

Related Questions