UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Editing .db Table With Sqlite Browser    
 
   
likajoho
post Sep 16 2013, 11:13 AM
Post#1



Posts: 1,069
Joined: 18-February 04
From: Oklahoma


I need to modify a .db database. Can't use Access 2010, so I've downloaded SQLite Browser. I can view the tables and alter them--one by one. I need to modify 2600 rows. Can someone give me a quick tutorial on how to create a query to change a phrase within a field? I need to change the path listed where the info is stored. So the query must add a couple of subfolders to the path existing, but leave the latter part of the path intact.
The database is called "RevisedMM.db"
The table is "Songs"
The field is "SongPath"
The change needed is to change the first part of the path ":\Music\" to :\Users\UserName\Music\
I want search the web for hours and still not figure out the correct syntax for this particular software. So any help would be appreciated.
Go to the top of the page
 
DanielPineault
post Sep 16 2013, 12:09 PM
Post#2


UtterAccess VIP
Posts: 5,454
Joined: 30-June 11



Perhaps the following link (several clear example of the proper syntax) might help you: http://www.coronalabs.com/blog/2012/04/03/...cess-in-corona/
Go to the top of the page
 
jleach
post Sep 17 2013, 03:41 AM
Post#3


UtterAccess Editor
Posts: 9,815
Joined: 7-December 09
From: Staten Island, NY, USA


sqlite has a built it replace() function, so you should be able to run the following query:
PDATE Songs SET SongPath = replace(SongPath, ':\Music\', ':\Users\UserName\Music\'
That said, I'm not very familiar with sqlite browser (there's numerous browser/management tools available), but for something like this you can probably just as easily go through the sqlite exe file.
sqlite Commandline/Shell Reference
hth
Go to the top of the page
 
likajoho
post Sep 17 2013, 07:53 AM
Post#4



Posts: 1,069
Joined: 18-February 04
From: Oklahoma


Thanks, but the link didn't work for me.
inda
Go to the top of the page
 
jleach
post Sep 17 2013, 08:22 AM
Post#5


UtterAccess Editor
Posts: 9,815
Joined: 7-December 09
From: Staten Island, NY, USA


which link...
Go to the top of the page
 
DanielPineault
post Sep 17 2013, 09:13 AM
Post#6


UtterAccess VIP
Posts: 5,454
Joined: 30-June 11



Can you post what you have tried so we can try and help you debug.
When you say the link didn't work, which one? what didn't work? Can you elaborate.
Go to the top of the page
 
likajoho
post Sep 18 2013, 07:44 AM
Post#7



Posts: 1,069
Joined: 18-February 04
From: Oklahoma


Daniel, it was your link that didn't work, but this morning it worked. But it doesn't tell how to replace a string within a string, so that didn't help much.
Hth, I can't really post anything. It's a huge database and it would be illegal anyway for me to post it since it is not a database I created.
Ofound a query and tried it. It used the UPDATE function. It didn't work and the program locked up. I can't even find it now to repeat and see if I messed up my syntax or something.
Does anyone know anything about RazorSQL? Maybe I should try that since SQLLite Browser has no way to easily search and replace, I can't figure out the syntax, and this RazorSQL apparently does have a search and replace tool.
Thank you so much for reading and responding.
Go to the top of the page
 
jleach
post Sep 18 2013, 08:31 AM
Post#8


UtterAccess Editor
Posts: 9,815
Joined: 7-December 09
From: Staten Island, NY, USA


Hi,
think you can look up some basics on an "UPDATE query" and find parts of the answers you need. The "UPDATE function" you refer to is likely a type of query that runs with the purpose of updating information per the query instructions. In my previous post, I have an example of an update query similar to one that you would need:
CODE
UPDATE Songs SET SongPath = replace(SongPath, ':\Music\', ':\Users\UserName\Music\')

SQL is a query language which uses various keywords, such as SELECT (to gather records), UPDATE (to update records), INSERT (to insert) and DELETE to perform actions against data. This is a standard language that's in use by just about every database system since long ago. You would be doing yourself a huge favor to read some basic information on the topic.
Odoubt you will find any tool that will do this for you using only graphical elements - a basic understanding of SQL will be required to perform the task.
Cheers,
Go to the top of the page
 
DanielPineault
post Sep 18 2013, 08:40 AM
Post#9


UtterAccess VIP
Posts: 5,454
Joined: 30-June 11



I'm not asking you to post the database. But you can post the SQL statement you have tried, so we can see what syntax you have tried, so we can troubleshoot it.
Go to the top of the page
 
likajoho
post Sep 18 2013, 04:32 PM
Post#10



Posts: 1,069
Joined: 18-February 04
From: Oklahoma


Found this syntax and tried it:
PDATE Songs
SET SongPath= REPLACE(SongPath, '\Music\', '\Users\UserName\Music\')
WHERE Names LIKE '%\Music\%'
Program quit working and closed. Obviously not a valid query.
Go to the top of the page
 
likajoho
post Sep 18 2013, 05:23 PM
Post#11



Posts: 1,069
Joined: 18-February 04
From: Oklahoma


Downloaded RazorSQL's trail version, but no go. I was able to create a query or set of queries I should say, but I got:
xecution Halted: An error occurred while executing statement 1
<code>UPDATE Songs SET SongPath = ':\Users\UserName\Music\SongName.mp3' WHERE rowid = 10848 AND ID = 10848</code>
Error: near line 1: no such collation sequence: IUNICODE
Owonder if I could export the table and then report import it without changing anything but what I want to change.
Go to the top of the page
 
likajoho
post Sep 18 2013, 05:36 PM
Post#12



Posts: 1,069
Joined: 18-February 04
From: Oklahoma


Yes I could export and modify the data, but importation was way too complicated and time consuming. It would be easier to change every row by hand. Which, is a distinct option at this point. Just hate to do that when I know there is a better way. I wish Access could open it.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 05:52 AM