fine tune my UPDATE stt pls ! - SQL Server Forums

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)

fine tune my UPDATE stt pls !

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-01 : 08:11:34

Maha writes "Hi

I have the following query working fine.I want OWNER_TYPE = "A" to be updated in my #rps_125_temp table. If there r three rows with owner_type = 'A' its picking up the last row. I want it to pick up the first row. I can use min(owner.owner_id) which will pick the first row waht I want.
That is
Owner table

owner_id owner_name owner_type
400 aaa A
401 bbb A
403 ccc A
404 ddd P

My existing query update the TEMP table with 403 ccc.

I want 400 and aaa in my TEMP table and I MUST WANT owner_type = 'A'

How can I incorporate the same here.

Kindly help

update #rps_125_temp set
addl_owner_first_name = owner.owner_first_name,
addl_owner_last_name = owner.owner_last_name,
addl_owner_initial_name = owner.owner_initial_name,
addl_owner_name_suffix = owner.owner_name_suffix from
#swis_125_temp,owner,parcel_to_owner,#rps_125_temp where
(#swis_125_temp.swis_co = parcel_to_owner.swis_co and
#swis_125_temp.swis_town = parcel_to_owner.swis_town and
#swis_125_temp.swis_vg = parcel_to_owner.swis_vg and
#swis_125_temp.roll_yr = parcel_to_owner.roll_yr) and
(#rps_125_temp.swis_co = parcel_to_owner.swis_co and
#rps_125_temp.swis_town = parcel_to_owner.swis_town and
#rps_125_temp.swis_vg = parcel_to_owner.swis_vg and
#rps_125_temp.parcel_id = parcel_to_owner.parcel_id) and
(owner.swis_co = parcel_to_owner.swis_co and
owner.swis_town = parcel_to_owner.swis_town and
owner.swis_vg = parcel_to_owner.swis_vg and
owner.parcel_id = parcel_to_owner.parcel_id and
owner.owner_id = parcel_to_owner.owner_id and
owner.owner_type = 'A') and
(year(parcel_to_owner.sale_date) = #swis_125_temp.roll_yr and
parcel_to_owner.book = '')"

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-02 : 22:53:44

Try this one. I've re-written your query to make use of INNER JOINs to join your tables. To get the minimum owner.owner_id where owner_type = 'A', look at the ones in red.

UPDATE A
SET
addl_owner_first_name = C.owner_first_name,
addl_owner_last_name = C.owner_last_name,
addl_owner_initial_name = C.owner_initial_name,
addl_owner_name_suffix = C.owner_name_suffix
FROM #rps_125_temp A
INNER JOIN parcel_to_owner B
ON A.swis_co = B.swis_co AND
A.swis_town = B.swis_town AND
A.swis_vg = B.swis_vg AND
A.parcel_id = B.parcel_id AND
B.book = ''
INNER JOIN owner C
ON B.swis_co = C.swis_co AND
B.swis_town = C.swis_town AND
B.swis_vg = C.swis_vg AND
B.parcel_id = C.parcel_id AND
B.owner_id = C.owner_id AND
C.owner_type = 'A' AND
C.owner_id = (SELECT MIN(owner_id) FROM owner E
WHERE E.owner_type = 'A')
INNER JOIN #swis_125_temp D
ON B.swis_co = D.swis_co AND
B.swis_town = D.swis_town AND
B.swis_vg = D.swis_vg AND
B.roll_yr = D.roll_yr AND
year(B.sale_date) = D.roll_yr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources