Updating field in table - 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

SQL Server Development (2000)

Updating field in table

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2008-04-24 : 07:14:00

I have 3 tables and 1 view. Which are:

TOWNLAND_GEOREFERENCE_POLYGON
PlanningPointLocation
paflarea

VIEW_paapplic

The View paaplic has 100 records and I have to do the below for all 1000 records individually.
I have to update the field TP_Total in the TOWNLAND_GEOREFERENCE_POLYGON table depending on what is in the fields in the tables.
I am writing the below code but am unsure if this is going to achieve what I want.

BEGIN

Select file_number, land_use_code, pluse1_code
From VIEW_paapplic

BEGIN
If pluse1_code = 'A' Then

Select TP_Total From TOWNLAND_GEOREFERENCE_POLYGON
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number

Update TOWNLAND_GEOREFERENCE_POLYGON SET TP_Total As TP_Total + 1
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number

Else If pluse1_code = 'C' Then
Select Count(*) As TempCount From table paflarea
Where fk_paapplicfile_nu = file_number

Select TP_Total From TOWNLAND_GEOREFERENCE_POLYGON
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number

Update TOWNLAND_GEOREFERENCE_POLYGON SET TP_Total As TP_Total + TempCount
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number

END

Anyone any ideas?
macca

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 07:26:24

We need some idea of structure of your table/views and some sample data with your expected o/p to help you on this. Could you please provide them?

macca
Posting Yak Master

146 Posts

Posted - 2008-04-24 : 07:46:44

Structure of View:
3 fields file_number varchar(8), land_use_code varchar(2), pluse1_code varchar(2)
Sample data view: file_number could be 12345, land_use_code could be 01, pluse1_code could be A or B or C

Structure of TOWNLAND_GEOREFERENCE_POLYGON:
Townland varchar(100), TP_Total int(4)
Sample data: Townland = Arlington, TP_Total = 3

Structure of PlanningPointLocation:
File_Number varchar(20)
Townland varchar(20)
Sample Data: File_Number = 12345, Townland = Arlington

Structure of paflarea:
fk_paapplicfile_nu varchar(8)
Sample data: fk_paapplicfile_nu = 12345

Expected op would be for pluse1_code = 'A' that in table TOWNLAND_GEOREFERENCE_POLYGON TP_Total would go from 3 to 4.
And op for pluse1_code = 'A' that in table TOWNLAND_GEOREFERENCE_POLYGON TP_Total would go from 3 to some number greater than 4 depending on TempCount From table paflarea.

Thanks,
macca

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 11:33:17

Try this:-

UPDATE tgp
SET tgp.TP_Total =tgp.TP_Total +
                  CASE 
                   WHEN vp.pluse1_code='A' THEN 1
                   WHEN vp.pluse1_code='C' THEN p.tempcount
                  END
FROM TOWNLAND_GEOREFERENCE_POLYGON tgp
INNER JOIN PlanningPointLocation ppl
ON ppl.Townland=tgp.Townland
INNER JOIN VIEW_paapplic vp
ON vp.file_number=ppl.File_Number
INNER JOIN (SELECT fk_paapplicfile_nu,COUNT(*) AS tempcount
            FROM paflarea 
            GROUP BY fk_paapplicfile_nu) p
ON p.fk_paapplicfile_nu= ppl.File_Number

macca
Posting Yak Master

146 Posts

Posted - 2008-04-25 : 06:39:34

Thanks visakh16,

This works fine WHEN vp.pluse1_code='A'.
But WHEN vp.pluse1_code='C' it does not update the appropriate tgp.TP_Total. In fact what it does it sets tgp.TP_Total = NULL for any with vp.pluse1_code='A'.

Thing is when vp.pluse1_code='C' then p.tempcount could be up to 50.

Any idea how to fix this?

macca

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 11:33:48

didnt get u. can you explain a bit more?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources