Insert values only if an id exists in another table

Question

Basically, the following is what I wish to do:

INSERT INTO table
(
    column1, column2
)
VALUES
(
    ?, ?
)
WHERE EXISTS
(
    SELECT 1
    FROM table2
    WHERE id = ?
)

In other words: provided an id exists in table2, the values should be inserted, otherwise nothing should happen.

What is the correct syntax for this task?

check this out: http://stackoverflow.com/questions/2513174/sql-server-insert-into-select-to-avoid-duplicates — Black Sheep, Sep 07 '13 at 20:16

score 2 · Answer 1 · answered Sep 07 '13 at 20:40

2

I found that this does what I need.

INSERT INTO table1
(
    column1, column2
)
SELECT ?, ?
FROM
table2
WHERE EXISTS
(
    SELECT 1
    FROM table2
    WHERE id = ?
)
LIMIT 1

answered Sep 07 '13 at 20:40

user2180613

739
6
21

score 2 · Answer 2 · answered Sep 07 '13 at 20:47

2

I have no mysql database available but similar to other databases and after reading the reference it must be something like

INSERT INTO table (column1, column2)
    SELECT ?, ? FROM table2 WHERE id=?

answered Sep 07 '13 at 20:47

Volker Seibt

1,479
16
19

score 0 · Answer 3 · answered Apr 27 '14 at 15:36

I know this is a pretty old question that's already been answered, but a friend of mine helped set up a database with a "constraint" where an insert or update had to have 1 value match one value from a different table in the same database exactly in order to fire off.

I'm not very good with MySQL calls yet so I don't understand exactly what it does... but it may help you cut down on the code necessary to do what you want? (Note: I had to modify it a bit to remove some sensitive names & such from it, but it should be a good representation.)

-- -----------------------------------------------------
-- Table `db1`.`table1`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `db1`.`table1` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `av_key` VARCHAR(40) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `db1`.`table2`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `db1`.`table2` (
  `bkid` INT NOT NULL AUTO_INCREMENT ,
  `id` INT NOT NULL ,
  `data` VARCHAR(6144) NULL ,
  PRIMARY KEY (`bkid`) ,
  INDEX `id_idx` (`id` ASC) ,
  CONSTRAINT `id`
    FOREIGN KEY (`id` )
    REFERENCES `db1`.`table1` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

With this, you just need to do the insert/update statement, and it'll just fail if there's no match for "id" in both tables.

Don't know if you already found out or if that's what you needed, but figured it was worth sharing!

Insert values only if an id exists in another table

3 Answers3