CODEWITHSUNDEEP
oracle-database
Abhijeet Panchgam
Abhijeet Panchgam

Reputation: 33

Oracle - Update COUNT of rows with specific value

This question has been posted several times but I am not able to get it work. I tried the approach mentioned in Update column to the COUNT of rows for specific values in another column. SQL Server. It gives me SQLException: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row error not sure what I can do.

Below is my problem 

UPDATE dataTable
 SET ACCX = (select b.cnt
               from dataTable a 
                 join
                (SELECT Account,
                       COUNT(1) cnt 
                  FROM  dataTable
                  GROUP BY Account) b 
                  on a.Account=b.Account)
        ,ACCR = 15481
        ,ACCF = 3
  WHERE ID = 1625

I only have the access & can change to the bold part since rest of the query is generated by the tool I cannot change it & I have to update ACCX column with the count of the value in column Account. Is it possible to do?

Note: - Account column is already populated with values.

Upvotes: 0

Views: 913

Answers (1)

jose_bacoy
jose_bacoy

Reputation: 12684

You cannot follow that query because it is for sqlserver and NOT oracle. It is simpler in oracle and does not need a join to itself.

This update will set the count for id 1625 only based on number of account numbers in datatable. See demo here; http://sqlfiddle.com/#!4/d154c/1

update dataTable a
set ACCR = 15481, 
    ACCF = 3, 
    a.ACCX = (
        select COUNT(*)
        from dataTable b
        where b.Account=a.Account)
WHERE a.ID = 1625;

Upvotes: 1

Related Questions