Update foreign key column with another table's primary key

Question

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;

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;

You are joining the two tables on subtype.id = supertype.id, but you also have a column subtype.supertype_id. Are you sure you didn't mean to join on this column? — Jacob H, Mar 07 '18 at 17:06
@JacobH If I do, there won't be a JOIN until after the update, so the update will never happen. — user1032531, Mar 07 '18 at 17:10
Oh I think I see what you're trying to do. Is PublicId a unique value for all the supertype records? That's your join predicate here. — Jacob H, Mar 07 '18 at 17:17
Sample data, before and after, would make it much easier to understand what you are trying to do. As it is now, I can't tell if your problem is in your query, your data, or how you've phrased your question. — Wes H, Mar 07 '18 at 17:23
@JacobH `idPublic` is unique for all records with a given `account_id`. — user1032531, Mar 07 '18 at 17:24
Also, supertype.id = subtype.id, what is the purpose of also having subtype.supertype_id? It seems redundant; if supertype.id = subtype.id, wouldn't subtype.id always be the same value as subtype.supertype_id? — Wes H, Mar 07 '18 at 17:25
@WesH the part about `idPublic`, `account_id`, and that the two tables have a `1-to-1` relationship is all real. I don't believe other data is relevant and was concerned that it would obscure the issue at hand. — user1032531, Mar 07 '18 at 17:27
@WesH No, it is a different record. The tables represent real energy meters and virtual energy meters. For instance, I have real meters `Building A Meter` and virtual meter `Building A 12 Month Meter`. Both have a record in the supertype table who's PK is joined to each's PK, however, the subtype table for the virtual meter needs to reference the real `Building A Meter`. While I am happy to discuss this part, I don't think it is relevant to what I am asking, but I suppose providing it is best as it removes the "why are you doing this" part. — user1032531, Mar 07 '18 at 17:33
@user1032531 You need to provide sample data along with schema for all of the tables and columns that you think might be involved. The information that you've provided is not enough to answer your questions or discuss your suggestions. Give us executable scripts documenting what you've tried, the result you want, and the result you're getting. Putting it in SQLFiddle is very helpful. — Wes H, Mar 08 '18 at 13:52

score 4 · Accepted Answer · answered Mar 03 '21 at 20:32

4

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

answered Mar 03 '21 at 20:32

john williams

56
2

Thanks John, Looking over what I originally posted, realize I wasn't very clear, don't anymore recall what I was thinking, and am going to go with this! – user1032531 Mar 03 '21 at 22:23

Update foreign key column with another table's primary key

1 Answers1