Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2010
    Posts
    87

    Unanswered: Update Query Predicate - How do you do it?

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2010
    Posts
    87
    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!

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

    RB

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    hokay might be some confusion here
    Should the red name be "foldername"?
    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
    Code:
    update [one big table] set brnd_logo = "/medlogs/" & brnd_logo where brnd_logo not like "/medlogs/*" and brnd_logo <>""
    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'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2010
    Posts
    87
    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?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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 <>""
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2010
    Posts
    87
    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];

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2010
    Posts
    87
    "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...

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    update [one big table]
    set brnd_logo = "/medlogs/" & brnd_logo
    where brnd_logo not like "/medlogs/*" and brnd_logo <>""
    Google
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Aug 2010
    Posts
    87
    Got it

    Code:
    update [one big table]
    set brnd_logo = "/medlogs/" & brnd_logo
    where brnd_logo not like "/medlogs/*" and brnd_logo <>""
    Where = Where as
    Not Like = Doesn't have within its constraints

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no
    Code:
    where brnd_logo not like "/medlogs/*" and brnd_logo <>""
    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.

    Code:
    brnd_logo <>""
    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
    Code:
    where brnd_logo not like "/medlogs/*" and brnd_logo <>""
    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 %.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Aug 2010
    Posts
    87
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •