Tips on optizming my sql Code 1

[HoustonGuy]

I feel like I have a lot of code that could definitely be written better.

Here is one example:

I have 20 fields that need updating from a temp table (#Final) that has a numeric counter field (MyCount).
Currently I have 20 sets of updates for each field.

UPDATE MainTable 
SET    Field1 
FROM 	#Final A
JOIN 	MasterTable B
ON 	A.IDnum = B.IDnum
WHERE   A.Mycount = 1

UPDATE MainTable 
SET    Field2 
FROM 	#Final A
JOIN 	MasterTable B
ON 	A.IDnum = B.IDnum
WHERE   A.Mycount = 2

UPDATE MainTable 
SET    Field3 
FROM 	#Final A
JOIN 	MasterTable B
ON 	A.IDnum = B.IDnum
WHERE   A.Mycount = 3

etc...

THis looks sloppy to me. I feel that I should be able to easily iterate a single update statement through a number variable that loops from 1 to 20. But i don't think Field+@variableCOunter will work without writing dynamic code. Dunno. The fact that the field number must match the mycount number throws a kink it in it.

Lastly, i program in a bubble a lot. (NO interaction with other programmers for best practice discussions.) Is there a site that lets you look for alternatives to the code. Something where I could get suggestions to replace update statements?

i'm probably not describing that well.
Thank you in advance!!!

 

[fredericofonseca]

something around these lines - note that you didnt state what you were updating the field to

UPDATE B
SET    Field1 = case when a.mycount = 1 then xxx else field1 end
     , Field2 = case when a.mycount = 2 then yyy else field2 end
FROM 	#Final A
INNER JOIN MasterTable B
ON 	A.IDnum = B.IDnum
WHERE   A.Mycount in (1,2,3)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bborissov]

The design of the table is not right.
Why you have a different field for each account?
What if you need one more account or 10 or 100?


Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

Your desciption of your data says more clearly than your code so far you really have an array of numbered columns. This points out your table design is not normalized.

That leads you to the central term for database design: Normalisation. It does not only help avoiding redundance, it sometimes makes queries needing more joins, but it prevents you from needing some dynamically created query with compositing field names having a numeric index as suffix.

If you have account1, account2, account3, or even just address1, address2, rather put that as 1:N related detail table with 1. it's own ID (primary key) 2. a foreign ID pointing back to the table it came from or is a detail from and 3. the data colummn(s). That's one of the simple normalsation rules.

Bye, Olaf.

 

[HoustonGuy]

Thanks fredericofonseca - I did fail to show the set value.

The table is highly denormalized, yes. It's a proprietary format for a flat file creation. I don't control the table design.

I'll test case suggestion.
Thanks everyone!

 

[Olaf Doschke]

OK,, if that's the design for exporting to flat file and you need to make such an update, then I assume this is created from other tables and it seems this step to update some of the fields can be easier done in an earlier stage.

Bye. Olaf.