CODEWITHSUNDEEP
mysqlsql
user3733831
user3733831

Reputation: 2936

MySQL INSERT INTO SELECT issue

I have two MySQL tables as below:

CREATE TABLE IF NOT EXISTS `item` (
  `item_id` INT NOT NULL AUTO_INCREMENT,
  `item_code` VARCHAR(45) NOT NULL,
  `item_name` VARCHAR(80) NOT NULL,
  `price` DECIMAL(16,2) NOT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE INDEX `item_code_UNIQUE` (`item_code` ASC)
) ENGINE = InnoDB CHARSET=utf8;

INSERT INTO item VALUES 
(1, '02', 'item A', '10.00'),
(2, '03', 'item B', '20.00'),
(3, '04', 'item C', '30.00');


CREATE TABLE IF NOT EXISTS `price` (
  `price_id` INT NOT NULL AUTO_INCREMENT,
  `price` DECIMAL(16,2) NOT NULL,
  `item_id` INT NOT NULL,
  PRIMARY KEY (`price_id`),
  INDEX `fk_item_price_item1_idx` (`item_id` ASC),
  CONSTRAINT `fk_item_price_item1`
    FOREIGN KEY (`item_id`)
    REFERENCES `item` (`item_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB CHARSET=utf8;

My question is, Just I need to insert data into price table from item table. In this case, it can not be duplicated the price in price table. I tried it like a query as below, but it is inserting duplicate price. Other thing is here I can't use ON DUPLICATE KEY UPDATE query.

INSERT IGNORE INTO `price` (`price`,`item_id`)
  SELECT DISTINCT `i`.`price`, `i`.`item_id`
    FROM `item` `i`
    INNER JOIN `price` `p` ON `p`.`item_id` = `i`.`item_id`;

The result of this query is:

+----------+-------+---------+
| price_id | price | item_id |
+----------+-------+---------+
|        1 | 10.00 |       1 |
|        2 | 20.00 |       2 |
|        3 | 30.00 |       3 |
|        4 | 10.00 |       1 |
|        5 | 20.00 |       2 |
|        6 | 30.00 |       3 |
|        7 | 10.00 |       1 |
|        8 | 20.00 |       2 |
|        9 | 30.00 |       3 |
+----------+-------+---------+
9 rows in set (0.000 sec)

Hope somebody may help me out.

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

To ensure that each item/price is unique, you can use a unique index or constraint:

CREATE UNIQUE INDEX unq_price_itemid_price ON price(itemid, price);

Note that there probably should not be a price in the item table. That is suspicious.

If you only want to ensure that your particular update doesn't insert duplicates, then you would use NOT EXISTS:

INSERT INTO price (price, item_id)
    SELECT i.price, i.item_id
    FROM item i
    WHERE NOT EXISTS (SELECT 1
                      FROM price p
                      WHERE p.item_id = i.item_id AND
                            p.price = i.price
                     );

SELECT DISTINCT is not needed because item_id is unique in item.

Upvotes: 2

Related Questions