Setting new column values based on primary key lookup in another table

Question

I have a table I want to add an extra column to, where the values in the column are defined by primary key lookup on another table.

I can do this easily by creating a new table with the original table, and the desired extra column:

CREATE TABLE FG_LABELLED
SELECT FG.*, L.PRODUCT_ALIAS
FROM LABEL_LOOKUP AS L INNER JOIN FG
ON FG.P_ID = L.P_ID;

But I cannot figure out the right way to do this by adding a column to the existing table, as per so:

ALTER TABLE FG
    ADD COLUMN PRODUCT_ALIAS VARCHAR(27);

UPDATE FG SET PRODUCT_ALIAS =  EXPRESSION;

What can I use here for expression that accomplishes the same effect as creating a new table based on an inner join?

I can't figure out how to ensure the inner join is going to be used to stitch the labels on in the right place, in this context. If indeed this can be done.

When I try

UPDATE  FG
    SET P_LABEL= (SELECT LABEL_LOOKUP.PRODUCT_ALIAS 
    FROM LABEL_LOOKUP INNER JOIN FG ON FG.P_ID = LABEL_LOOKUP);

I get

Error code 1093. You can't specify target table 'FG' for update in FROM clause.

What is the right way to do what I am trying to do? Or is it not possible to do it?

thanks,

Heather

p.s. I am new to SQL

Answer 1

Your syntax for the update should look like this:

UPDATE FG 
INNER JOIN LABEL_LOOKUP ON FG.P_ID = LABEL_LOOKUP.P_ID
SET FG.P_LABEL=LABEL_LOOKUP.PRODUCT_ALIAS;