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)  Some tips on a update?

Author  Topic  Kode Starting Member 23 Posts Posted - 2001-06-30 : 16:57:58 I have a big wall in front of me. I am trying to update a table inside SQL 7 as a job because I need to do it once a day. Now what I am have is two tables, tblCharts & tblCharts_Yesterday. Each night I move tblCharts to tblChartsYesterday and then create the new tblCharts table for that day. After moving and creating the new table I am trying to figure out how to do an update that will join the tables together by a songID and update tblCharts with yesterdays chart rank from tblCharts_Yesterday. Here is some sql that will do this to give you a better picture of what I mean. SELECT C.todaysRank, C.songID, CY.todaysRank AS yesterdaysRank FROM tblCharts AS C JOIN tblCharts_Yesterday AS CY on (C.songID = CY.songID) ORDER by C.todaysRank Now I obviously don't know much SQL, I know I can program a loop in an asp page using ADO to do multiple updates, but I would like to have the SQL server do this work load. As or right now I have ASP doing this each time the page is called and it is a big speed problem. I would like to have the work done once a day by SQL and this way all I have to do is query the table to display all my chart info. I can't figure out how to do a loop style update in SQL and even worse how I can do the update without the join data, since I can't update a join. I am not looking for code, I have a few SQL books but I can't find what I am looking for, if you could point me to a command or theory so that I can go learn this, that would be great.