Quite new to all of this..
I have two tables (names simplified)
PersonTable

FactTable

Clearly I need to run 2 separate UPDATE queries
One to update the NamePrefix where FactType = 42, and
One to update the NameSuffix where FactType = 36

Each PersonID in the FactTable may have more than one entry, but usually only one for each Fact Type (unless there’s an error somewhere in the data – which I’m not worried about for now)

What I tried
what I am running

UPDATE PersonTable
SET NamePrefix = PersonTable.NamePrefix || " " || 
(
SELECT  FactTable.FactDetails
    FROM PersonTable
    INNER JOIN FactTable 
    ON PersonTable.PersonID = FactTable.OwnerID 
    WHERE FactTable.FactType='42'
    AND FactTable.OwnerID = PersonTable.OwnerID
);

What I am expecting to see in the Table after the first run (Fact Type=’42’)
| PersonID | NamePrefix |NameSuffix|
| ——– | ——– | ——–
|2 | Capt. |Null
|3 | Sir |Nul

and What I am expecting to see in the Table after running the second run (Fact Type=’36’)
| PersonID | NamePrefix |NameSuffix|
| ——– | ——– | ——–
|2 | Capt. |Null
|3 | Sir |Lord of Somewhere

HOWEVER
The result I am getting is that All instances of the Person Table are being populated by the first matching record in the FactTable (that = 42) so everyone has a prefix of CAPT whether they have any entries in the Fact Table or not.

Yet, when I run only the Select Part,

SELECT  PersonTable.OwnerID, FactTable.FactDetails --(I need that extra first field in there to check the right person)
    FROM PersonTable
    INNER JOIN FactTable 
    ON PersonTable.PersonID = FactTable.OwnerID 
    WHERE FactTable.FactType='42'
    AND FactTable.OwnerID = PersonTable.OwnerID

I get a correct list of only those people in the PersonTable, having a corresponding fact, of the correct type (42)

As I have said, I am new to this so probably a very rookie mistake that I just can’t see. I have tried several snippets from various to get to this point