updating a field based on a range of values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

updating a field based on a range of values

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2004-02-09 : 20:59:42

I have a table ABC which has 3 fields (dept, acct_number and acct_type). Another table XYZ which has the following fields (dept, beg_acct, end_acct).


sample table ABC
DEPT      ACCT_NUMBER     ACCT_TYPE
----      -----------     ---------
H         600020           NULL
H         720001           NULL
T         001934           NULL
T         003003           NULL
T         015023           NULL

sample table XYZ
DEPT	BEG_ACCT	END_ACCT
----    ---------       --------
H	600000	       629999
S	500100	       509999
T	002000	       012999
T	016100	       016199
T	020200	       021299

For each record in table ABC, the acct_type needs to be updated. The way this should happen is... look for the dept for each record on table ABC and corresponding range of account numbers (between beg_acct, end_acct)in table XYZ with the same department_code (there could be more than one range for each dept). If the acct_number is in that range, it should be updated as valid else invalid.

AFTER THE UPDATE THE TABLE SHOULD LOOK LIKE
[CODE]
DEPT ACCT_NUMBER ACCT_TYPE
---- ----------- ---------
H 600020 VALID
H 720001 INVALID
T 001934 INVALID
T 003003 VALID
T 015023 INVALID
[/CODE]
How do I approach this?

Any help will be greatly appreciated.

Thank you.
PKS.

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-09 : 22:59:42

Sounds like a homework problem


SELECT X.DEPT -- This'll be NULL if it's INVALID, NOT NULL IF VALID
  FROM ABC A
    LEFT OUTER JOIN XYZ X ON X.DEPT = A.DEPT and A.ACCT_NUMBER BETWEEN X.BEG_ACCT AND X.END_ACCT

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-09 : 23:07:26

If it is an update statement you're looking for then this might do.
UPDATE ABC
SET ACCT_TYPE = 'VALID'
FROM ABC
JOIN XYZ ON ABC.DEPT = XYZ.DEPT
WHERE ABC.ACCT_NUMBER BETWEEN XYZ.BEG_ACCT AND XYZ.END_ACCT

UPDATE ABC
SET ACCT_TYPE = 'INVALID'
WHERE ACCT_TYPE IS NULL

sahu74
Posting Yak Master

100 Posts

Posted - 2004-02-10 : 00:23:22

Thank you so much Ditch and Sam. Felt really dumb realizing that 'between' syntax didnt occur to me.
Thanks anyway.
PKS.

quote:
Originally posted by ditch

If it is an update statement you're looking for then this might do.
UPDATE ABC
SET ACCT_TYPE = 'VALID'
FROM ABC
JOIN XYZ ON ABC.DEPT = XYZ.DEPT
WHERE ABC.ACCT_NUMBER BETWEEN XYZ.BEG_ACCT AND XYZ.END_ACCT

UPDATE ABC
SET ACCT_TYPE = 'INVALID'
WHERE ACCT_TYPE IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources