SQL Command not properly ended in update statement

Question

Have 2 tables, INVN_SBS and INVC_ITEM.
Looking to set the one field in the table to active = '0' based on criteria of if INVN_SBS.ITEM_SID and INVNSBS.ITEM match and INVN_SBS.DCS_CODE = 'SMP'

So far i have:

UPDATE INVN_SBS 
SET ACTIVE='0' 
FROM 
   INVN_SBS,
   INVC_ITEM 
WHERE 
       INVN_SBS.ITEM_SID = INVC_ITEM.ITEM_SID
   and INVN_SBS.DCS_CODE='SMP'

However when run I get:

ORA-00933:SQL Command not properly ended.

Am able to run a select statement to show me the item that match my criteria but the update is giving trouble.

Oracle doesn't support the `from` clause in an `update`. Either use `merge` or a correlated subquery. — Gordon Linoff, Aug 04 '14 at 23:01

score 1 · Accepted Answer · answered Aug 05 '14 at 01:15

This is one possible solution:

UPDATE INVN_SBS 
SET ACTIVE='0' 
where
  DCS_CODE='SMP' and
  ITEM_SID in (
    select ITEM_SID
    from INVC_ITEM
  )

If your primary keys support it, this should also work:

UPDATE (
  select INVN_SBS.ACTIVE
  FROM 
    INVN_SBS,
    INVC_ITEM 
  WHERE 
    INVN_SBS.ITEM_SID = INVC_ITEM.ITEM_SID
    and INVN_SBS.DCS_CODE='SMP'
)
SET ACTIVE='0'

If your primary keys aren't supportive of this second approach, it will let you know at compile-time.

SQL Command not properly ended in update statement

1 Answers1