Dennis Wrote:
>If you want to update the AlbumName field, you must do that with an
>update statement running on the Album table, not the Music table, since
>that is where the AlbumName field is stored. You haven't said what you
>want to update the AlbumName or ArtistName to. You probably have a
>condition, that you also haven't described, that selects which records
>in the table to update. Generally it will look something like this.
k.say there are 40000 records in the MUSIC table This table has only the
Album_id and not the Albumname .But If I want to update all the AlbumName
for all the records in MUSIC table to only one AlbumName say 'Album1' then
the rest of the AlbmName has to be deleted in the ALBUM Table and Album1 Id
should be provided as Album_Id for all the records in the MUSIC table.
I think then I have to delete all the records in the ALBUM Table and insert
one new record with the new AlbumName.Then I have to update Albim_Id for all
the records in the MUSIC table.Am I right or is there any other way.
>If this isn't what you are looking for, you will have to describe your
>problem in more detail (i.e. what you are trying to do, an example of
>before and after data, etc.) before anyone can provide more assistance.
>Original Table Before Update:
"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT
NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));"
ArtistId ArtistName YomiArtistName
10 bbb BBB
11 xxx XXX
12 aaa AAA
"CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id
INTEGER);"
Id Track YomiTrack URL Album_Id Artist_Id
1 trak1 TRAK1 c:/trak1 22 10
2 song SONG c:/song 21 11
3 abc ABC c:/abc 23 12
Delete * from ARTIST.
Insert into ARTIST (ArtistName,YomiArtistName) values ('Album1');
Update MUSIC SET Album_Id = ( select Albumid from ALBUM where ArtistName
='Album1');
Modified Table After Update:
"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT
NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));"
ArtistId ArtistName YomiArtistName
1 Album1 ALBUM1
"CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id
INTEGER);"
Id Track YomiTrack URL Album_Id Artist_Id
1 trak1 TRAK1 c:/trak1 1 10
2 song SONG c:/song 1 11
3 abc ABC c:/abc 1 12
Similarly I will change the artist name of all the records also.
Thanks & Regards,
Mahalakshmi
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
