CODEWITHSUNDEEP
oracle-databasesql-update
BetterLateThanNever
BetterLateThanNever

Reputation: 906

Oracle - Update from another table

Given:

USERS:
UserID  TypeID  GroupID     CreatedDt   UpdatedDt
1       1       1           01/01/20    01/01/20
2       2       2           01/01/20    01/01/20
3       3       2           01/01/20    01/01/20

TYPES:
TypeID  GroupID
1       1
2       2
3       3

I want to update GroupID in USERS table that doesn't match with the TYPES table

UPDATE 
     u
SET 
     u.GroupID = t.GroupID,
     u.UpdatedDt = CURRENT_DATE
FROM 
     USERS u,
     TYPES t
WHERE 
     u.TypeID = t.TypeID 
     AND u.GroupID <> t.GroupID;

Error: Failing at line "FROM"

Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

What am I doing wrong ?

Upvotes: 1

Views: 81

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You want to update rows from a query result. The syntax for this is:

update 
(
  select
    u.groupid as u_groupid,
    t.groupid as t_groupid,
    u.updateddt as u_updateddt
  from users u
  join types t on t.typeid = u.typeid
              and t.groupid <> u.groupid
)
set u_groupid = t_groupid,
    u_updateddt = sysdate;

(I've replaced current_date with sysdate, as it is much more likely you want this. If not, replace it back. I've also replaced your comma-separated join with an explicit join, as has been common for the last 20+ years. Explicit joins were introduced in the SQL standard in 1992.)

Upvotes: 0

EJ Egyed
EJ Egyed

Reputation: 6064

The syntax for your update statement is incorrect. You can not join tables in an update statement.

Try an update like this one.

UPDATE users u
   SET u.GroupID =
           (SELECT t1.GROUP_ID
              FROM types t1
             WHERE t1.TYPE_ID = u.TYPE_ID),
       u.UpdatedDt = CURRENT_DATE
 WHERE EXISTS
           (SELECT 1
              FROM types t2
             WHERE t2.type_id = u.type_id AND t2.GROUP_ID <> u.GROUP_ID);

Update

If you don't want to use nested subqueries, you could try performing a MERGE statement to apply the changes you want.

MERGE INTO users u1
     USING (SELECT u2.userid, t.groupid
              FROM types t, users u2
             WHERE t.typeid = u2.typeid AND t.groupid <> u2.groupid) d
        ON (u1.userid = d.userid)
WHEN MATCHED
THEN
    UPDATE SET u1.groupid = d.groupid, u1.updateddt = CURRENT_DATE;

Upvotes: 1

Related Questions