CODEWITHSUNDEEP
mysqlsql
pravallika
pravallika

Reputation: 7

Error while updating rows in a table

I am getting an error "SQLCODE: -811" while updating the rows. I know the error was getting due to my inner query is returning multiple values. I need to know how to correlate the query properly. Please help me in the query!

update hpl.kamodt C
    set (NRANK, chghlightacces) = 
             (select NRANK, chghlightacces
              from hpl.kamodt as A
                  inner join (
                      select naccesgrpref, NMOD, count(*)
                      from hpl.kamodt
                      where naccesgrpref is not null
                        and NMOD is not null
                      group by naccesgrpref, NMOD
                      having count(naccesgrpref) > 1) B
                  on  A.naccesgrpref = B.naccesgrpref 
                  and A.NMOD = B.NMOD  
                  and A.naccesgrpref = A.NACCES)

The inner query is checking for the value which is having a group and selecting rank, accesgrpref for those columns. I have to set these values for the parts whose naccesgrpref is equal to the naccesgrpref value from the innerquery.

Please help me in this query.

Upvotes: 1

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Presumably, you intend something like this:

update hpl.kamodt C
    set (NRANK, chghlightacces) = 
             (select NRANK, chghlightacces
              from (select naccesgrpref, NMOD, count(*)
                    from hpl.kamodt
                    where naccesgrpref is not null and NMOD is not null
                    group by naccesgrpref, NMOD
                    having count(naccesgrpref) > 1
                   ) B
              where C.naccesgrpref = B.naccesgrpref and
                    C.NMOD = B.NMOD
             )
    where C.naccesgrpref = C.NACCES;

This is (intelligent) speculation. A correlated subquery should refer to the table in the outer query. Your version has no correlated, so the fact that it returns duplicates is unsurprising.

Upvotes: 2

Related Questions