CODEWITHSUNDEEP
sqloracle-database
user3306994
user3306994

Reputation: 13

Update specific rows in Oracle using the count from another table

I am trying to update some records in one table (street) with a count from another table (house). I am trying to update the house_count in the street table with the correct number of houses on that street. I only want to update the records that are incorrect. I have been able to do this is MSSQL using the code below:

CREATE TABLE street
(
name varchar(255),
house_count int
);

Create table house
(
id varchar(255),
street_name varchar(255)
);

insert into street values ('oak',1)
insert into street values ('maple',2)
insert into street values ('birch',4)
insert into street values ('walnut',1)

insert into house values (1,'oak')   
insert into house values (2,'oak')
insert into house values (1,'maple')
insert into house values (2,'maple')
insert into house values (1,'birch')
insert into house values (2,'birch')
insert into house values (3,'birch')
insert into house values (1,'walnut')

update s set s.house_count= hc.ActualCount
from street s
inner join
(select s.name, count(s.name) as 'ActualCount', s.house_count
from street s
inner join house h on s.name=h.street_name
group by s.name, s.house_count
having count(s.name) <> s.house_count) hc ON s.name=hc.name
where s.name=hc.name

I have a need to do something similar in Oracle but have ran into issues. From what I have found the join is not possible in Oracle but I am having a hard time getting something that will work. Any help in getting something like this to work in Oracle is greatly appreciated.

Thanks

Upvotes: 1

Views: 2021

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can do this with a correlated subquery:

update street
    set house_count = (select count(s.name)
                       from house h
                       where street.name = h.street_name
                      );

This is a little different from your approach, because it will update all streets, even when the count does not change. There is no performance advantage using a subquery in trying to prevent the update.

EDIT:

This should solve the problem with the apartment streets versus house streets:

update street
    set house_count = (select count(s.name)
                       from house h
                       where street.name = h.street_name
                      )
    where exists (select 1 from house h where street.name = h.street_name);

Upvotes: 1

Related Questions