Problems with the UPDATE command

[Rodney Cocker]

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.

I performed several tests and I can change either all the records on the F: drive or all the records on the H: drive but not both.

It seems that after the first command, it won't work a second time.

I even tried:

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 results in none of the records being changed.

Any ideas as to the cause of this behaviour?

TIA
     
_________________________________________________________________
Looking to move this spring? With all the lastest places, searching has never been easier. Look now!
http://clk.atdmt.com/NMN/go/157631292/direct/01/
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Igor Tandetnik]

Rodney Cocker <rodneycocker@...>
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@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[P Kishor-3]

On Thu, Nov 5, 2009 at 1:28 PM, Rodney Cocker <rodneycocker@...> 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.
>
> I performed several tests and I can change either all the records on the F: drive or all the records on the H: drive but not both.
>
> It seems that after the first command, it won't work a second time.
>
> I even tried:
>
> 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 results in none of the records being changed.
>
> Any ideas as to the cause of this behaviour?
>
>

No enough information to generate any ideas whatsoever. From what you
describe, the following works for me

sqlite> CREATE TABLE local_episodes (EpisodeFilename TEXT);
sqlite> INSERT INTO local_episodes VALUES ('F:\somefile');
sqlite> INSERT INTO local_episodes VALUES ('F:\someother file');
sqlite> INSERT INTO local_episodes VALUES ('H:\a file on h drive');
sqlite> INSERT INTO local_episodes VALUES ('H:\another file on h drive');
sqlite> SELECT * FROM local_episodes;
EpisodeFilename
---------------
F:\somefile
F:\someother file
H:\a file on h drive
H:\another file on h drive
sqlite> UPDATE local_episodes
   ...> SET EpisodeFilename = 'j:' || Substr(EpisodeFilename, 3,
Length(EpisodeFilename))
   ...> WHERE Substr(EpisodeFilename, 1, 3)= 'F:\';
sqlite> SELECT * FROM local_episodes;
EpisodeFilename
---------------
j:\somefile
j:\someother file
H:\a file on h drive
H:\another file on h drive
sqlite> UPDATE local_episodes
   ...> SET EpisodeFilename = 'j:' || Substr(EpisodeFilename, 3,
Length(EpisodeFilename))
   ...> WHERE Substr(EpisodeFilename, 1, 3)= 'H:\';
sqlite> SELECT * FROM local_episodes;
EpisodeFilename
---------------
j:\somefile
j:\someother file
j:\a file on h drive
j:\another file on h drive
sqlite>



--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, Wisconsin, United States
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Rodney Cocker]

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@...
> From: itandetnik@...
> Date: Thu, 5 Nov 2009 14:42:30 -0500
> Subject: Re: [sqlite] Problems with the UPDATE command
>
> Rodney Cocker <rodneycocker@...>
> 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@...
> 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@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users