0

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.

jose_bacoy
  • 12,227
  • 1
  • 20
  • 38
Abhijeet Panchgam
  • 33
  • 1
  • 2
  • 5

1 Answers1

1

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;
jose_bacoy
  • 12,227
  • 1
  • 20
  • 38