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.
| Author |
Topic |
|
luke.wilkinson
Starting Member
4 Posts |
 Posted - 2011-12-08 : 03:19:18
|
| I have a linker table that has two columns:SongID & ArtistIDI can insert new records to this by a stored procedure that has @SongID and @ArtistID. The store procedure is being called from a c# codebehind in a loop of Artists but is only ever the one songID. So I can just check if the ArtistID exists agains the SongID and if not I can insert.But I also need to delete any records that do not match the artisits in the loop. I have thought I could had a modfied date column to the table then just update that for every record in the loop then delete any that the date is older than say 10mins. But I don't feel this is the best way. Can anyone suggest a better way of doing this in SQL? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-08 : 06:58:18
|
| you mean delete all records for other artists which are not considered in current loop? whats that for? doesnt sound like a good approach to me------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
luke.wilkinson
Starting Member
4 Posts |
Posted - 2011-12-08 : 07:16:37
|
| Delete all records that song no longer is associated with that artist. So EG. SoongID = 1 and is currently connected to artist 1, 2 and 3. Later the song has been updated so it now has artist 4 but artist 1 needs to be removed. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-08 : 07:36:56
|
| ok for that whay you can do isDELETE FROM LinkTable WHERE SongID=@SongID AND ArtistID <> @ArtistID------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
luke.wilkinson
Starting Member
4 Posts |
Posted - 2011-12-08 : 08:09:03
|
| The problem with that is it's only checking one artistID. The stored procedure is being called in a loop so ther could be 3 artistID's against that songID but when you check it you are only checking it against one of the artistID's? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-08 : 08:48:56
|
quote:
Originally posted by luke.wilkinson
The problem with that is it's only checking one artistID. The stored procedure is being called in a loop so ther could be 3 artistID's against that songID but when you check it you are only checking it against one of the artistID's?
ok then put the @artistIDs inside a table variable inside loop @Artists and then after loop do followingDELETE lFROM LinkTable lLEFT JOIN @Artists aON a.ArtistID = iArtistIDWHERE l.SongID=@SongID AND a.ArtistID IS NULL ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
luke.wilkinson
Starting Member
4 Posts |
Posted - 2011-12-08 : 09:58:15
|
| I looked at your suggestion but I am using SQL 2005 and you can't use table varibles as parameters in 2005... :( |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
Delete records that don't match