near "."; syntax error using JOIN in UPDATE query-CodePudding

Using SQLite 3.39.3 in DBeaver I am trying :

UPDATE Tbl_RawDataPART 
SET p.MessageClean = w.Vertaling, p.GedetecteerdeTaal = w.Taal
FROM Tbl_RawDataPART as p
INNER JOIN Tbl_WoordenNietVertalen as w ON p.message = w.Woord 
WHERE p.GedetecteerdeTaal  Is Null

I get :

SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (near "."; syntax error)

The same query in Microsoft Access works:

UPDATE Tbl_RawDataPART INNER JOIN Tbl_WoordenNietVertalen 
ON Tbl_RawDataPART.message = Tbl_WoordenNietVertalen.Woord 
SET Tbl_RawDataPART.MessageClean = [Tbl_WoordenNietVertalen]![Vertaling], Tbl_RawDataPART.GedetecteerdeTaal = [Tbl_WoordenNietVertalen]![Taal]
WHERE (((Tbl_RawDataPART.GedetecteerdeTaal) Is Null));

A SELECT in DBeaver works:

SELECT *
    FROM Tbl_RawDataPART as p
    INNER JOIN Tbl_WoordenNietVertalen as w ON p.message = w.Woord 
    WHERE p.GedetecteerdeTaal  Is Null

If I do UPDATE I get the same error:

UPDATE Tbl_RawDataPART 
SET p.MessageClean = "x" 
WHERE p.GedetecteerdeTaal is null

CodePudding user response：

Your query would be syntactically correct if you remove the p. qualifier from the updated columns in the SET clause:

UPDATE Tbl_RawDataPART 
SET p.MessageClean = w.Vertaling, p.GedetecteerdeTaal = w.Taal
FROM Tbl_RawDataPART as p
INNER JOIN Tbl_WoordenNietVertalen as w ON p.message = w.Woord 
WHERE p.GedetecteerdeTaal  Is Null;

But, I believe that you don't need the extra join to Tbl_RawDataPART.
This is how you write a join-like UPDATE statement with the UPATE...FROM syntax in SQLite:

UPDATE Tbl_RawDataPART AS p
SET MessageClean = w.Vertaling, 
    GedetecteerdeTaal = w.Taal
FROM Tbl_WoordenNietVertalen AS w 
WHERE w.Woord = p.message AND p.GedetecteerdeTaal IS NULL;