Update Statement Issue

Question

I have constructed what I thought was a simple update statement, but keep running into: General SQL error. ORA-00933: SQL command not properly ended

Statement:

UPDATE        TEAM_MEMBER_TEMPLATE
SET           TEAM_MEMBER_TEMPLATE.TITLE = TEAM_MEMBER.TITLE
FROM          TEAM_MEMBER_TEMPLATE
INNER JOIN    TEAM_MEMBER
ON            TEAM_MEMBER_TEMPLATE.TEAM_MEMBER_ID= TEAM_MEMBER.ID

Here's what I got to work previously to make sure the table/field could be updated:

UPDATE TEAM_MEMBER_TEMPLATE
SET TITLE = 'TEST'
WHERE ID = 38

Where am I going wrong here? Running Oracle version 12.1.0.1.0

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-01-17T00:08:50

Oracle doesn't support FROM in the UPDATE. You can use a correlated subquery:

UPDATE TEAM_MEMBER_TEMPLATE TMT
    SET TITLE = (SELECT TM.TITLE
                 FROM TEAM_MEMBER TM
                 WHERE TMT.TEAM_MEMBER_ID = TM.ID
                );

Note: It is generally a bad idea to repeat data like this in the database. Instead, you should just use JOIN when you want the title.

Update Statement Issue

1 Answers