Copying Data From One Table To Another 1

[ryandoah]

Hi All,

I'm building a web based killboard from a game that I play online. The site accepts a text message that is sent to the player in game, describing the details of the kills. I have written a script that parses the data then submits it to the database. Problem is, I designed the db improperly.

I have several tables for the different types of data that is parsed: Kills, Victims, Involved Parties, and Destroyed Items.

The Involved Parties and Destroyed Items are related to the Kills table by the Kills.ID(Primary Key), but the Victim and Kills table are related by the Victims.ID(Primary Key).

I didn't realize the problem until I after I began accepting data from players and started to build the searching features for the site.

What I need to do is copy the Kills.ID into the Victims table where the Victims.VictimID = Kills.VictimID, so that I can search all tables related to a kill in the same manner, eg; SELECT * FROM Victims, InvolvedParties, Destroyed Items WHERE KillID = <a kills.ID>.

How do I do this? I currently have about 600 records in the db, and don't want to lose the data.

Thank you.

ryandoah

 

[mrdenny]

First add a KillsID to the Victims table.

Then:

update Victims
set Victims.KillsID = Kills.ID
from Kills
where Kills.VictimID = Victims.VictimID

Your select statement should be something like this.

select *
from victims
join InvoicedParties on Victims.KillsID = InvoicedParties.KillsID
join Destroyed on Victims.KillsID = Destroyed.KillsID
where Victims.KillID = @KillID

Be sure to make a backup of the database before you modify the data so that if something goes wrong you can restore and try again.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[ryandoah]

I knew I was close. I was missing the line "FROM Kills".

Thank you.