Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update master record with rowcount

Posted on 2010-09-23
5
Medium Priority
?
367 Views
Last Modified: 2012-06-27
(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
0
Comment
Question by:LFreehauf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 33744822
this should do;
update x
  set sfields9 = 1 + ( 
    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.hmytenant 
       or R.hmytenant = x.hmytenant 
  )  
 from tenant x

Open in new window

0
 

Author Comment

by:LFreehauf
ID: 33745095
Theory Looks correct.   However, Cut and paste created an error;  I'm happy to modify and further test after this "unexpected meeting".  Thanks for the fast response.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33746364
what error do you get?
0
 

Author Comment

by:LFreehauf
ID: 33746478
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')
0
 

Author Closing Comment

by:LFreehauf
ID: 33746493
Very good.  Very fast.  Expert follow up when I indicated a problem with exact syntax.  
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question