CODEWITHSUNDEEP
mysqlsql
user1032531
user1032531

Reputation: 26281

Update foreign key column with another table's primary key

Basically, I need to update a column in one table based on a column's value in another table. This question has been asked at least a couple times here and every answer with high votes states to join the two tables and set the value of a column in one table based on the value of the applicable column in the other table:

It doesn't seem to work for me, and either I am not seeing things correct or my conditions are different.

EDIT 1. I didn't show it, but supertype also has an accounts_id column, and think that is the column I will be joining on.

I have a supertype and subtype table where the subtype table includes a second field called supertype_id which stores a supertype record's PK (schema shown below).

I need to update subtype.supertype_id for a given subtype record (i.e. subtype.id=123) with a supertype table's PK based on some other attribute of the supertype table (i.e. supertype.publicId=321).

So, I originally thought I would need some sort of JOIN, however, didn't update any rows as the JOIN doesn't yet exist.

UPDATE subtype
INNER JOIN supertype ON supertype.id=subtype.id
SET subtype.supertype_id=supertype.id
WHERE subtype.id=123 AND supertype.idPublic=321;

UPDATE subtype
INNER JOIN supertype ON supertype.id=subtype.id AND supertype.idPublic=321
SET subtype.supertype_id=supertype.id
WHERE subtype.id=123;

I could likely do something like the following, however, question doing so as it differs from all highly voted answers and it also attempts to set suptype.supertype_id to NULL if supertype doesn't have a record with the given idPublic which will result in a foreign key constraint. 

UPDATE subtype SET supertype_id = SELECT id FROM superset WHERE idPublic=321;

How should this be implemented?

EDIT 2. Maybe something like this?

UPDATE suptype sbt
INNER JOIN supertype spt1 ON spt1.id=sbt.id
INNER JOIN supertype spt2 ON spt2.accounts_id=spt1.accounts_id
SET sbt.supertype_id =sbt2.id
WHERE sbt2.idPublic=321 AND sbt.id=123;

enter image description here

CREATE TABLE supertype (
  id INT NOT NULL AUTO_INCREMENT,
  data VARCHAR(45) NULL,
  publicId INT NOT NULL,
  PRIMARY KEY (id),
  INDEX publicIdIdx (publicId ASC))
ENGINE = InnoDB;

CREATE TABLE subtype (
  id INT NOT NULL,
  supertype_id INT NOT NULL,
  data VARCHAR(45) NULL,
  PRIMARY KEY (id),
  INDEX fk_subtype_supertype1_idx (supertype_id ASC),
  CONSTRAINT fk_subtype_supertype
    FOREIGN KEY (id)
    REFERENCES supertype (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_subtype_supertype1
    FOREIGN KEY (supertype_id)
    REFERENCES supertype (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Upvotes: 3

Views: 4188

Answers (1)

john williams
john williams

Reputation: 56

You need to SELECT that particular value. If you had a recipe that stored the type of flour used as an id from a table of flours, but you have a form that allowed a user to select the type, not the type id, you would convert the user's selection to the foreign table's id something like this.

UPDATE TableRecipe
SET BakeMinutes = 90,
    FlourTypeID = (SELECT FlourTypeID FROM TableFlourTypes WHERE Type = 'AllPurpose')
WHERE RecipeID= 34

Upvotes: 4

Related Questions