CODEWITHSUNDEEP
oracle-databasesql-updateora-01427
Slingy
Slingy

Reputation: 71

update set value based on another value of another column and/or same column in another row:-ORA 1427

I'm trying to set a column to reset to zero or increment by +1 based on a pass or fail in another column, and/or the value of that same column in the previous weeks row.

There are two other variable columns which must match those in the previous weeks row.

Table is something like:

WEEK  |   ID1   |   ID2   | FLAG   | INCREMENT_COUNT  |
--------------------------------------------------------


--------------------------------------------------------

I have been trying to get this part of the procedure to work, and the best I've got so far is:

 ID_IN and ID_IN3 are passed in the procedure call
 OLD_DATE and NEW_DATE are set as the previous week and current week
----------------------------------------------------------------------         
update table1
set table1.INCREMENT_COUNT = CASE
                                 WHEN table1.FLAG is null then null
                                 WHEN table1.FLAG = 1 then 0
                                 WHEN table1.FLAG = 0 then (NVL(INCREMENT_COUNT,0)+ 1)
                                 END
where (select INCREMENT_COUNT
  from table1
  where WEEK=NEW_DATE
  and ID1=ID_IN
  and exists (select (1)
              from table2
              where table1.ID2=table2.ID2
              and table2.ID3=ID_IN3))
 =
(select INCREMENT_COUNT
  from table1
  where WEEK=OLD_DATE
  and ID1=ID_IN
  and exists (select (1)
              from table2
              where table1.ID2=table2.ID2
              and table2.ID3=ID_IN3));

When this procedure is called I get the error

ORA-01427: single-row subquery returns more than one row

Additionally, in MySQL I could do it something like this and get it working...

update table1 as t01
left join(select ID3, ID2, INCREMENT_COUNT as prev_count from table1 as t10 inner join table2 as t2 on t10.ID2=t2.ID2 where ID1=ID_IN and ID3=ID_IN3 and t10.WEEK=OLD_DATE) as prev_date on t01.WEEK=NEW_DATE and prev_date.ID2=t01.ID2 and t01.ID1=ID_IN

set t01.INCREMENT_COUNT = if(t1.FLAG is null, null, if(t1.FLAG,0, IFNULL(prev_date.prev_count,0)+1))
where t01.ID1=ID_IN
and t1.WEEK=NEW_DATE
and prev_date.ID3=ID_IN3;

Upvotes: 0

Views: 2201

Answers (3)

Slingy
Slingy

Reputation: 71

This seems to have done the job. Thanks for the help, it got me thinking in a different way. 

 UPDATE TABLE1 T01

            SET INCREMENT_COUNT = CASE 
                                    WHEN T01.FLAG IS NULL THEN NULL
                                    WHEN T01.FLAG = 1 THEN 0
                                    WHEN T01.FLAG = 0 THEN (NVL((SELECT INCREMENT_COUNT 
                                                                        FROM TABLE1 T10
                                                                        WHERE T10.WEEK=OLD_DATE
                                                                        AND T01.WEEK=NEW_DATE
                                                                        AND T01.ID2=T10.ID2
                                                                        AND ID1=ID_IN),0)+ 1)
                                    END
            WHERE EXISTS (SELECT (1)
                      FROM TABLE2
                      WHERE TABLE1.ID2=TABLE2.ID2
                      AND TABLE2.ID3=ID_IN3);

Upvotes: 0

unleashed
unleashed

Reputation: 771

Similarl to your mySQL example, you can do something like this in oracle. This may not work for you depending on your data model. I've put together a crude basic version based on your information, but you've not provided enough information about your data model and your tables/aliases/column names are poor for readability...

(more on update with a subquery here -> https://docs.oracle.com/database/121/SQLRF/statements_10008.htm#i2067871)

update 
    (select t01.increment_count, t01.flag, prev_date.prev_count
    from  table1 t01
    left join(select ID3, ID2, INCREMENT_COUNT as prev_count 
                from table1 t10 
              inner join table2  t2 on t10.ID2=t2.ID2     
              where ID1=ID_IN 
               and ID3=ID_IN3 
               and t10.WEEK=OLD_DATE) prev_date on t01.WEEK=NEW_DATE  and prev_date.ID2=t01.ID2 and t01.ID1=ID_IN
    where t01.ID1=ID_IN
    and t1.WEEK=NEW_DATE
    and prev_date.ID3=ID_IN3)
set INCREMENT_COUNT = if(FLAG is null, null, if(FLAG,0, IFNULL(prev_count,0)+1));

Upvotes: 1

NikNik
NikNik

Reputation: 2301

One of the queries in where condition returns more than 1 record

Upvotes: 0

Related Questions