CODEWITHSUNDEEP
sqloracle-database
amine tabenyoussef
amine tabenyoussef

Reputation: 3

How to update a column with a date in Oracle?

I need to update a specific column with the current date. This is my query:

update  a
set a.x_date =(select CAST(sysdate AS DATE)as f from dual)
from RIM I 
JOIN INV P ON P.ID = I.PK_ID
JOIN ODF a ON a.ID = I.ID  
JOIN SRM R ON R.USER_ID = I.ASSIGNED_TO 
WHERE I.TYPE_CODE = 'ISSUE'  
AND I.ID = 2222;

I get this error message: "SQL command not properly ended"

Upvotes: 0

Views: 123

Answers (2)

Sandeep
Sandeep

Reputation: 806

update  a
set a.x_date =SYSDATE
from RIM I 
JOIN INV P ON P.ID = I.PK_ID
JOIN ODF a ON a.ID = I.ID  
JOIN SRM R ON R.USER_ID = I.ASSIGNED_TO 
WHERE I.TYPE_CODE = 'ISSUE'  
AND I.ID = 2222;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Oracle does not allow JOIN in an UPDATE. You can use conditions in the WHERE instead:

update odf
    set x_date = sysdate
    where exists (select 1
                  from RIM I join
                       INV P
                       on P.ID = I.PK_ID join
                       SRM R
                       on R.USER_ID = I.ASSIGNED_TO 
                  where I.TYPE_CODE = 'ISSUE' AND I.ID = 2222 and
                        I.ID = odf.ID
                 );

There is no reason to case sysdate to a date.

Upvotes: 3

Related Questions