Chosing which table to update by using a variable

[penguinspeaks]

This is a basic question I am sure but do not know the answer nor can I find it while searching.

If I have tables based on numbers: table_1, table_2, table_3

I want to update a specific table based on a variable that will indicate which table to update.

For example, if
mytable_id = 3 and I wish to update this table, how would I write the sql?

strSQL = "update table_&mytable_id& set test_ = 4" obviously does not work. I need the syntax for the table_(variable) to make this work please.

THanks

Bam

 

[djj55]

Can you use a CASE statement?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Qik3Coder]

You were close.
the RTRIM is a "cheap" way to convert to a string. (I'm assuming it's a number)

DECLARE strSQL [b]nvarchar(max)[/b]
Select strSQL = "update table_" + rtrim(mytable_id) + " set test_ = 4"
sp_executeSQL strSQL

You've got questions and source code. We want both!
There's a whole lot of Irish in that one.

 

[penguinspeaks]

Qik3Coder:

Thanks, I believe this is what I need.

djj55 :

This is going to be a site that runs multiple tournaments for various things. Tables will be created based on the next available ID. All of the links and pages will be based on this ID as well so a case wouldn't work here, at least I do not think so.

 

[Qik3Coder]

BB,
I'd suggest shying away from that model.

There are better ways of "partitioning" your data, while still being able to maintain integrity.
The TournamentID should be an attribute of whatever table you're working with.

If you are concerned about clogging your table, then create a process that cleans out the old rows after 1 day/week/month/year.

Lodlaiden

You've got questions and source code. We want both!
There's a whole lot of Irish in that one.

 

[penguinspeaks]

The plan I envision is this.

Once a tournament is complete, there will be no changes to the database based on the ended tournament so I do not need that reference. I will have to be able to display the players that were in the tourney and the brackets so I plan to somehow convert the asp page with the final results to an HTML page for future reference. THis way I can clear the data about this tournament from the database thus keeping it small and neat.

WOuld you suggest something else?

 

[Qik3Coder]

Are you storing some form of aggregate data for the specific tournament?

It sounds like you're going to have/want something like:

Player/Team:
1 | Player 1

Tournaments:
1 | Tourny 1 | [Start Date] | [End Date]
2 | Tourny 2 | [Start Date] | [End Date]

Player/Team Activity:
Player 1 | Won | 17-2
Player 2 | Lost | 17-2

Aggregate Data:
Player 1 | Tourny 1 | Won 3 | Lost 4
Player 2 | Tourny 2 | Won 7 | Lost 3

The aggregate data is calculated and loaded into the "Aggregate Data" post tournament
In this way you can delete all the actual Activity associated with the event, while still leaving results for review later.

Just my .02.

Lodlaiden


You've got questions and source code. We want both!
Here at tek tips, we provide a hand up, not a hand out.

 

[penguinspeaks]

This is somewhat what I could do. Each player would be registered so the stats would be updated there for each player such as a Win field, loss field, tourneys entered, ect,, so I do not think the player stats stored within the specific tourney would be needed.?
I am using a bracket system so individual stats per tourney would not be needed I do not think. However, if I decide to run "ROund RObin" Tourneys, this information would be vital to the structure.

As far as the tourney information, I do have a table that holds all of this such as the items you have listed. I also have location, format, and things like that.

I have the code for the reporting done from 8 team brackets single and double elimination up to 32 team single and double elimination. That was the tricky part..lol.

 

[Qik3Coder]

It sounded like you were green fielding this, so I was offering suggestions on areas that tend to bite later in the process (like reporting).
The only reason I offered the "aggregate" table was b/c I just finished a game and everyone likes to tout their score/position from the last X events.

G/L with your design.
Remember, Code that works is worth infinately more than code that looks pretty.

Lodlaiden

You've got questions and source code. We want both!
Here at tek tips, we provide a hand up, not a hand out.

 

[penguinspeaks]

Thanks for your help and suggestions. I truly appreciate it!

 

[gmmastros]

Please permit me a moment to offer my thoughts.

I agree with Qik3Coder. From a database design perspective, you should NOT be creating additional tables "on the fly" to store additional information.I say this based on my years of experience, both designing my own application and also assisting people on this site. Every developer I have ever talked to about this situation agrees that it is FAR better to have fewer tables with more rows, than more tables with fewer rows. The only exception to this rule is when you have a table with 100's of millions of rows and you are trying to optimize performance. From the sounds of it, this would not apply to your situation.

"This way I can clear the data about this tournament from the database thus keeping it small and neat."

There is nothing neat about having multiple tables similarly named (Table_1, Table_2, Table_3, etc...). The storage space for multiple tables will be slightly higher than it would be for a single table with an additional column to identify the tournament that the row applies to.

"All of the links and pages will be based on this ID as well"

"I will have to be able to display the players that were in the tourney and the brackets so I plan to somehow convert the asp page with the final results to an HTML page for future reference."

If you design your database properly, you would not need to convert your ASP pages to HTML pages. Each tournament would have an "end date". If the end date has already occurred, your ASP page could be shown in a "read-only" mode.

Properly designing a database is not necessarily intuitive to everyone. There are things to learn. I would strongly encourage you to spend 20 minutes or so learning about [google]database normalization[/google].

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[penguinspeaks]

Thank you for your input on this. If I understand you correctly, this is what I am understanding.

For the bracket positions, you are saying it is better to have a single table than it is to have a different table for each tournament.

For a 8 person bracket, there are 15 positions that appear on the bracket. You are saying it would be better to just add rows as I go, with a t_id to identify which position is for which tourney.
For example, instead of having a table called "tourney1, tourney2.." that has tpos_ field where the team_id goes,
it would be better to have a single table with the same field but add the t_id field.

Is this what I am understanding?

 

[Qik3Coder]

That's the gist.
You may need some special logic for storing which particular bracket position someone is in.
You may need something such as a table with all the particular spots for a bracket, and then fill them in manually as a team advances. This is more a problem for display than anything else.

You've got questions and source code. We want both!
Here at tek tips, we provide a hand up, not a hand out.

 

[penguinspeaks]

Got it, thanks a bunch!

 

[penguinspeaks]

Ok so I tried things and they did not work out.

For the sake of an argument, and if I choose to have more tables than we discussed, which I have not decided as of yet, how would I make this SQL work?

strSQL = "update bracket_'"&vformat2&"'_"&t_id&" set pos_ = "&vrnd&" where t_id = "&t_id&" and team_id = "&vnum&"

the t_id is showing up but the issue I have is the vformat2 is text and not a number.

when I response.write I get this:

the variable vformat2 = "se" for the record

update bracket_''_1 set pos_ = 4 where t_id = 1 and team_id = 1

how do I use the variable for the text part in this statement?

 

[penguinspeaks]

Cancel my last question, I have it all worked out now.

Thanks everyone for the tips and suggestions!

 

[djj55]

How did you solve it?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!