update oracle date column with different date values

2
votes

When updating the old dates to the new dates, I am getting

SQL Error: ORA-00932: inconsistent datatypes: expected DATE got CHAR 00932. 00000 - "inconsistent datatypes: expected %s got %s"

UPDATE test
   SET date1 = 
   CASE date1
    WHEN '22-OCT-19' THEN '23-OCT-19'
    WHEN '21-OCT-19' THEN '22-OCT-19'
    WHEN '20-OCT-19' THEN '21-OCT-19'
    WHEN '17-OCT-19' THEN '18-OCT-19'
    WHEN '06-OCT-19' THEN '07-OCT-19'
    WHEN '05-OCT-19' THEN '06-OCT-19'
    WHEN '04-OCT-19' THEN '05-OCT-19'
    WHEN '03-OCT-19' THEN '04-OCT-19'
    WHEN '02-OCT-19' THEN '03-OCT-19'
    WHEN '29-SEP-19' THEN '30-SEP-19'
    WHEN '27-SEP-19' THEN '28-SEP-19'
   END;
3
sqloraclesql-update
Your current query will update all other dates not included in the CASE to NULL. You better use a WHERE-condition with those dates and then simply add 1 day. – dnoeth
@dnoeth I believe that by using my example in my answer OP will avoid that... – VBoka

3 Answers

3
votes

As the error message implies, you mix dates and varchars.

Your current query will update all other dates not included in the CASE to NULL. You better use a WHERE-condition with date literals and then simply add 1 day:

UPDATE test
SET date1 = date1 + 1
WHERE DATE1 IN
 ( DATE '2019-10-22'
  ,DATE '2019-10-21'
  ,DATE '2019-10-20'
  , etc.
 )
0
votes

Does phrasing the logic using explicit DATE literals fix the problem?

UPDATE test
    SET date1 = (CASE date1
                     WHEN DATE '2019-10-22' THEN DATE '2019-10-23'
                     WHEN DATE '2019-10-21' THEN DATE '2019-10-22'
                     . . .
                 END);
0
votes

This will work:

update test 
set date1 = 
(
    case date1
       when to_date('22-OCT-19', 'dd-mon-yyyy') THEN to_date('23-OCT-19', 'dd-mon-yyyy')
       else date1
    end
);

Here is the DEMO