LFreehauf
asked on
Update master record with rowcount
(Optional Read) Business Objective: Count the number of roommates living with the primary tenant/resident and update a field on the resident record.
(Optional Read) Background: FYI, I’m comfortable with SQL select and update statements. I have minimal experience with cursors, stored procedures etc. (Of course) I use a test DB prior to applying to production environment.
(Must Read) The Primary Question for Experts:
Assuming the SQL statement provide below is on the right track, how should I modify the SQL statement to:
o Accumulate/Count the quantity of roommates for each of the 10000 tenant records
o Update the sfields9 field for each of the 10000 tenant records
I think I need to use a cursor (not confident).
Of course I will award points appropriately if my general direction is wrong and Expert(s) provide redirection.
SQL Statement: The statement below successfully updates the Tenant.sfields9 record with quantity of individuals living with the specified tenant “49546”. (Note the “49546” filters the select and update to one tenant/resident.) The successful solution should run without the where clause limiting to an individual tenant. It should run for all tenants, subtotaling the quantity of roommates for each tenant, and updating the sfields9 field with said subtotal.
select p.Sfirstname + ' ' + p.ulastname, t.hmyperson
From person p left outer join room r on p.hmy = r.hmyperson
left outer join tenant t on t.hmyperson = r.hmytenant
where
p.hmy in (49546) or R.hmytenant in (49546)
update tenant set sfields9 = (@@rowcount + 1) where hmyperson = 49546
(Optional Read) Table.Field Details:
1. Tenant.sfields9 = “Tenant” Table and “sfields9” field holds the quantity of individuals living in apartment.
2. “Room” table contains roommates id (xref to “Tenant” table)
3. “Person” table contains name, address, etc for both the tenant and room tables
Experts-Exchange---Update-Primar.doc
(Optional Read) Background: FYI, I’m comfortable with SQL select and update statements. I have minimal experience with cursors, stored procedures etc. (Of course) I use a test DB prior to applying to production environment.
(Must Read) The Primary Question for Experts:
Assuming the SQL statement provide below is on the right track, how should I modify the SQL statement to:
o Accumulate/Count the quantity of roommates for each of the 10000 tenant records
o Update the sfields9 field for each of the 10000 tenant records
I think I need to use a cursor (not confident).
Of course I will award points appropriately if my general direction is wrong and Expert(s) provide redirection.
SQL Statement: The statement below successfully updates the Tenant.sfields9 record with quantity of individuals living with the specified tenant “49546”. (Note the “49546” filters the select and update to one tenant/resident.) The successful solution should run without the where clause limiting to an individual tenant. It should run for all tenants, subtotaling the quantity of roommates for each tenant, and updating the sfields9 field with said subtotal.
select p.Sfirstname + ' ' + p.ulastname, t.hmyperson
From person p left outer join room r on p.hmy = r.hmyperson
left outer join tenant t on t.hmyperson = r.hmytenant
where
p.hmy in (49546) or R.hmytenant in (49546)
update tenant set sfields9 = (@@rowcount + 1) where hmyperson = 49546
(Optional Read) Table.Field Details:
1. Tenant.sfields9 = “Tenant” Table and “sfields9” field holds the quantity of individuals living in apartment.
2. “Room” table contains roommates id (xref to “Tenant” table)
3. “Person” table contains name, address, etc for both the tenant and room tables
Experts-Exchange---Update-Primar.doc
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what error do you get?
ASKER
update x
It works! Thanks.
I had to change x.hmytenant to x.hmyperson in the where clause. It updated (successful & accurate) on two tenant records. Thanks again. I appreciate it.
set sfields9 = (
select count(*)
from person p
left outer join room r
on p.hmy = r.hmyperson
left outer join tenant t
on t.hmyperson = r.hmytenant
where p.hmy = x.hmyperson
or R.hmytenant = x.hmyperson
)
from tenant x where x.scode in ('t0036179', 't0003407')
It works! Thanks.
I had to change x.hmytenant to x.hmyperson in the where clause. It updated (successful & accurate) on two tenant records. Thanks again. I appreciate it.
set sfields9 = (
select count(*)
from person p
left outer join room r
on p.hmy = r.hmyperson
left outer join tenant t
on t.hmyperson = r.hmytenant
where p.hmy = x.hmyperson
or R.hmytenant = x.hmyperson
)
from tenant x where x.scode in ('t0036179', 't0003407')
ASKER
Very good. Very fast. Expert follow up when I indicated a problem with exact syntax.
ASKER