Problem with MySQL updating a Foreign Key Where the Inner Join is Based on that same Foreign Key

I have a table (Staff) with columns of StaffId (PK), StaffName and StaffLocationId. LocationId is the foreign key to the Locations table. The Locations table has columns LocId (PK) and LocName.

In the Location table there are 2 locations with Id's of 1 and 2, and LocNames of London and Sydney respectively.

Alice is the name off a staff member, and her location Id is 1 (London). I want to move her to Sydney by updating her LocationId to 2 without having access to the Id directly. Obviously I can just do

UPDATE Staff 
SET StfLocationId = 2 
WHERE StaffName = 'Alice'

but I only have the name Sydney, not the Id of 2.

I would assume this is a perfectly normal and easy thing to do, but I can't for the life of me figure out why it is not working.

My latest attempt so far is:

UPDATE Staff AS S 
INNER JOIN Locations As L ON L.LocId = S.StfLocationId 
SET S.StfLocationId = L.LocId 
WHERE L.StfLocName = 'Sydney' AND S.StaffName = 'Alice'

I would be really grateful if anyone knows the answer to this, I've spent a long time trying various version, using aliases etc all without luck.

You should not be using an INNER JOIN, you should use CROSS JOIN, since this is not using the relationship between the two tables.

UPDATE Staff AS s
CROSS JOIN Location AS L
SET s.StfLocationId = L.LocId
WHERE s.StaffName = 'Alice' AND l.StfLocName = 'Sydney'

You can also use a subquery:

UPDATE Staff
SET StfLocationId = (
    SELECT LocId
    FROM Location
    WHERE StfLocName = 'Sydney'
)
WHERE StaffName = 'Alice'