Hello,

I have several seemingly simple update queries to perform on a dB. This one is about adding something to the beginning of every file name in a column of 59000 records.

I just want to add "/foldername/" to the beginning of each file name.

I'm new to access and loving update queries thus far but I'm just so new that I'm not well versed! I'll probably have a couple other posts coming soon too! If you care to tackle them

Thank you very much,
RB

update mytable set myfilename = "/foldername/" & myfilename where myfilename not like "/filename/*"

select the query designer
switch the view to SQL (left most button under 'file'
replace mytable with the name of the table
replace myfilename with the name of the column holding the filename
save the query
run the query

Robert should be your sisters brother

update mytable set myfilename = "/foldername/" & myfilename where myfilename not like "/filename/*"

select the query designer
switch the view to SQL (left most button under 'file'
replace mytable with the name of the table
replace myfilename with the name of the column holding the filename
save the query
run the query


Should the red name be "foldername"?

My table name is "ONE BIG TABLE" (WITH SPACES)
My field name is "BRND_LOGO"
Name of folder I want to add "/medlogos/"

This is what I have in SQL view. Looks off to me!


RB

hokay might be some confusion here
dunno its your table
you first say you want to prepend /foldername/
then you introduce /medlogs/
Im guessing that you want to prepend /medlogs/ to the value of the existing text in the column called BRND_LOGO.
in which case I'd suggest
to explain
the update [one big table] tells the SQL engine that we aare trying to do an update
the set brnd_logo = "/medlogs/" & myfilename tells the SQL engine to set the value of brnd_logo equal to "/medlogs/" and the existing content eg if brnd_logo is "dbforums.jpg" then the set command will transform that to "/medlogs/dbforums.jpg"
the where brnd_logo not like "/medlogs/*" and brnd_logo <>"" determines what rows shoudl be modified. in this example I'm limiting the rows where the value doesn't already start with "/medlogs/".. thats the not like '/medlogs/*' AND where there is somethign defined in the brnd_logo column. there'd be no point in prepending '/medlogs/ if there was nothing there already.

mind you naming a column brnd_logo and having that column on every row fires off warning signs that this table design is not normalised. even calling the table [one big table] suggests there's problems here.
why is it a problem if not normalised?
.. well say ferinstance you find the supplier issues a new logo and you decide to call it 'dbforumsnewlogo.png'. that means having to do an update query carefully specifying everything. if you define a table for suppliers and within that table define the logo then you can pull the logo as and when required fromt eh supplier table (assumign you have got a supplier ID that points to the supplier table. one of the mantras in realtional db design is store once.

the very fact that you have numeric columns defined as string is asking for trouble
why?
say you want to sort on price. if you store as text then the sort sequence will be £1.00, £10, £11.....£19, £2, £20.....29 and so on
if you sort on value it will be in ascending numeric order 1,2,3.......99
like wise dates shoudl be stored in datetime columns NOT string/text.
why?
because you cannot use the date time functions designed to make life easier manipulating dates.

I'm incredibly confused. Not because if you but because I may have written the wrong thing. Let's start over


My table name is "ONE BIG TABLE" (WITH SPACES)
My field name is "BRND_LOGO"
Text I want to add before the filenames "/medlogos/" (without quotes).


This is what I believe will add "/medlogos/" before all the file names in the field named BRND_LOGO in the table named "ONE BIG TABLE"

update ONE BIG TABLE set BRND_LOGO= "/foldername/" & BRND_LOGO where BRND_LOGO not like "/medlogos/*"


That right?

no that will prepend "/filename/" NOT "/medlogos/"
you have to encapsualte text / string literals with either quote or double quote marks so the SQL engine knows where the text starts and stops. engine will strip out surplus tags.

the SQL statement in the previous post should do the job.

if you want to check the SQL is there or there abouts run it against a separate table take a backup of the databse first

update [one big table] set brnd_logo = "/medlogs/" & brnd_logo where brnd_logo not like "/medlogs/*" and brnd_logo <>""

Thank you!


Based on your instructions, I applied this in an Update Query whereas "ONE BIG TABLE" is the table name and "BRND_LOGO" is the field name and "/medlogos/" is the desired prefix. My over explanation is intended to help new people to the forum, not you .


UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].BRND_LOGO = "/medlogos/" & [BRND_LOGO];

bear in mind that a SQL statement has lots of options and permutations, its a language in its own right, a very specialised language dealing with data manipualtion

update and delete statements can be very dangeroius in the hands of a novice, unless you limit the rows selected using a where clause then the action will be performed on EVERY row. think of a where clause as a filter
A WHERE clause is optional, as is an ORDER BY clause on a SELECT statement
http://www.google.co.uk/#hl=en&cp=20...w=1600&bih=815

Google

"unless you limit the rows selected using a where clause"

Is there a general indicator that a "where clause" is in place? Such as a word or certain syntax? I'm guessing the word "where" would probably be it. But as yo u know, I'm a novice...

Google

Got it

Where = Where as
Not Like = Doesn't have within its constraints

no
is the where clause, and there are two conditions that must be met (applied to the rows being returned

WHERE tells the SQL engine that effectivley you want to apply a filter
brnd_logo NOT LIKE "/medlogs/*"
is the first condition and uses two settings
we are telling the SQL engine to search for the pattern/match "/medlogs/*", thats the LIKE "/medlogs/*" bit but we only want those rows which do not have "/medlogs/*" in thats the NOT bit. we can use the wil card symbols *, or % if we are using ANSI SQL compatability mode, to say doesn't matter what is exists beyond the specified text
eg
"*rbusiness*" would mean match any row with rbusiness anywhere in it
"rbusiness*" would mean match any row with rbusiness at the start
"*rbusiness" would mean match any row with rbusiness at the end of it

more recnet databases implement regular expressions, which I think can be used in access/JET, buyt trust me you don't want to dablle in REGEX just yet.

is the second term, where we are saying the vlaue of the column brnd_logo should not equal "" ie not an emtpy string

you glue together the various conditions using OR or AND as the result of the where clause, much like an if statement is a boolean value so we use boolean operators

so the whole where clause is
as said elsehwere SQL is a complete language in its own right, dedicated to databases and manipulation of databases/data.

just to give you a flavour of SQL in SELECT statements have a read of this. Although SQL is now an ISO and ANSII standard virtually no manufacturer has a totally complaint SQL implemenation. not all have every SQL function, most if not all have there own little wrinkles. so whats good for MySQL version 5.5 may not be good for other MySQL versison, let alone DB2, Access/JET. SO dont' get to hung up about every SQL term in that reference, but it should give you a flavour of how an SQL statement can be built up and what it can comprise. BTW Access uses * as a wildcard byt default unless you tell it to use SQL compatability mode when the wild card symbol becomes %.

Excellent explanation.

Thanks for the link too. I read it. It's odd because it's so complicated in one read and then when you get the right doc, it's like "OHHHH" and I at least understand a little better.