Mahalakshmi.m wrote:
>
> "CREATE TABLE ALBUM(AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT
> NULL COLLATE NOCASE ,AlbumTrackCount INTEGER,UNIQUE(AlbumName));"
>
> AlbumId AlbumName AlbumTrackCount
> 1 aaa 3
> 2 ddd 2
> 3 ccc 1
>
> Here I am maintaining the Number of track for that particular Album in
> AlbumTrackCount. Bcoz I need to find the total number of track so instead of
> using "select Count(*) from MUSIC where Album_Id = 1 ;" I will just
> read
> the AlbumTrackCount from ALBUM table.This speed up my performance.
>
> "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
> NULL,Album_Id INTEGER);"
>
> Id Track Album_Id
> 1 t1 1
> 2 t2 1
> 3 t3 1
> 4 t4 2
> 5 t5 2
> 6 t6 3
>
> I want to update all the Album to some new name say 'xxx' then i have to
> delete all the records in ALBUM table and to insert one new Album with name
> as 'xxx' and the AlbumTrackCount should now become 7.After that I have to
> change the Album_Id in MUSIC also.
>
> So after updating
> AlbumId AlbumName AlbumTrackCount
> 1 xxx 7
> And all the Album_Id value should be 1.
>
> Can any one help to solve this.
>
I'm not sure why you want to do this, but if that's what you want to do
I would suggest using triggers to maintain the counts in the album
table. These triggers would also delete unreferenced records.
create trigger up_music_album after update of Album_Id on MUSIC
begin
update ALBUM set AlbumTrackCount = AlbumTrackCount - 1
where AlbumId = Old.Album_id;
update ALBUM set AlbumTrackCount = AlbumTrackCount + 1
where AlbumId = New.Album_id;
delete from ALBUM where AlbumTrackCount = 0;
end;
create trigger in_music after insert on MUSIC
begin
update ALBUM set AlbumTrackCount = AlbumTrackCount + 1
where AlbumId = New.Album_id;
end;
create trigger del_music after delete on MUSIC
begin
update ALBUM set AlbumTrackCount = AlbumTrackCount - 1
where AlbumId = Old.Album_id;
delete from ALBUM where AlbumTrackCount = 0;
end;
With these triggers in place your update becomes one update to change
the name of the album you want to retain, and another to set every music
record that doesn't already refer to that album so that it does refer to
the retained record. The update trigger will decrement the count of the
album records and remove them when they are no longer referenced by the
music table.
update ALBUM set AlbumName = 'xxx' where AlbumId = 1;
update MUSIC set Album_Id = 1 where Album_id != 1;
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
