Hi Igor,
Thanks for your reply. I have a database with a table in it called local_episodes, it has about 8000 entries in it. Each record in the table holds a reference to the location of a file on my computer. Currently the files are stored on 3 different drives (D:, F:, H:). I am doing some consolidation and file management tasks and wish to move all the files on my F & H drives to a new drive named J: So I want to run an update query that looks up all files that are listed in the database as being on either the F or H drives and change the location to be J: For example: Currently in the database I have the following entries: F:\movie\antarctica.avi H:\movie\iceblues.avi I wish to change these entries to read: J:\movie\antarctica.avi J:\movie\iceblues.avi In order to do this, I opened the database in SQLite Database Browser Clicked on the Execute SQL Tab Entered the following query: UPDATE local_episodes SET EpisodeFilename = 'j:' || SUBSTR(EpisodeFilename, 3,LENGTH(EpisodeFilename)) WHERE SUBSTR(EpisodeFilename,1,3)='F:\' This worked perfectly and as expected F:\movie\antarctica.avi became J:\movie\antarctica.avi I then tried entering the same SQL query but this time using H: in the where condition UPDATE local_episodes SET EpisodeFilename = 'j:' || SUBSTR(EpisodeFilename, 3,LENGTH(EpisodeFilename)) WHERE SUBSTR(EpisodeFilename,1,3)='H:\' This is where it didn't work, and by this I mean this resulted in no records being changed at all. There were no error messages, just no changes made. I then took the original database with none of the records changed and did this in reverse order ie I ran the update query with H in the where condition first. In this case the records were changed, but after that if I ran the update query with F in the where condition this also resulted in no records being changed and no error messages. It seems for whatever reason that the update query only works once on the table and after that if fails to have any effect. I thought I would be clever and so tried this again with a fresh copy of the database and this time I tried to change both F & H at the same time with the following update query. UPDATE local_episodes SET EpisodeFilename = 'j:' || SUBSTR(EpisodeFilename, 3,LENGTH(EpisodeFilename)) WHERE SUBSTR(EpisodeFilename,1,3)='F:\' OR SUBSTR(EpisodeFilename,1,3)='H:\' But this also resulted in no records being changed and no error messages. So whatever I attempt I only seem to be able to change the file locations for the files in one drive not both. I don't think the backslash is causing a problem because it works in the first instance. The only thing I haven't tried which I will give a go later tonight when I get home is to change all the references to files on the f drive to J and then see if I can change them back again to f. Any suggestions would be greatly appreciated. Regards Rodney > To: sqlite-users@sqlite.org > From: itandet...@mvps.org > Date: Thu, 5 Nov 2009 14:42:30 -0500 > Subject: Re: [sqlite] Problems with the UPDATE command > > Rodney Cocker <rodneycoc...@hotmail.com> > wrote: > > I ran the following SQL command > > > > UPDATE local_episodes > > SET EpisodeFilename = 'j:' || SUBSTR(EpisodeFilename, > > 3,LENGTH(EpisodeFilename)) > > WHERE SUBSTR(EpisodeFilename,1,3)='F:\' > > > > Which worked perfectly, but when I went to run it the next time to > > change any files stored on my H: drive it didn't work. > > Define "didn't work". What statement did you run, and how did the outcome > differ from your expectations? > > How do you run the statement? Be careful with that backslash - many > programming languages and shell environments treat those as escape characters > (SQLlite itself doesn't). > > Igor Tandetnik > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _________________________________________________________________ Looking for a date? View photos of singles in your area! http://clk.atdmt.com/NMN/go/150855801/direct/01/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
