Search code examples
sqlsql-serversql-server-cesql-server-ce-4

MSSQLCE: Subquery returns parsing error

I've added an ID column (personID) into a table (personList) instead of another field: personPin

Now I need to fill empty foreign key field (seenPersonID) of another table (personAttendances) 

UPDATE personAttendances
SET seenPersonID =
(SELECT personID FROM personList WHERE (personAttendances.personPin = personPin))

Why does SQL engine say there is an error parsing the query? Or how can I fill the field on other table?

Solution

  • You need something like

    UPDATE personAttendances 
SET seenPersonID = b.personID
FROM personAttendances a
INNER JOIN personList b ON (b.personPin = a.personPin)