Results 1 to 10 of 10
  1. #1
    Join Date
    May 2002
    Posts
    28

    Unanswered: Can't figure update query

    Can't figure the following out:



    I need to append records 11709-13847 of the field "Main_id" in my "FISHSRY_SCCT_MAIN" table to records 1-2139 (of Barrier_id) in another table(FISHSRY_SCCT_BARRIERS).

    I don't want to simply add on the records from "Main_id" to the end of the barriers table.

    How can I do this? I know I need to use an update query (?) but can't get the syntax/logic right. Would it be possible for you to show me the exact sql commands to use? This is my first time doing an update query using criteria expressions and I'm confused.

    The reason I want to do this is because the records in the Barriers table link to records in the Main table - they are linked by the "Main_id" field.

    Thanks for your help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you want to append, but you don't want to add them on to the end?

    that's too confusing for me

    can you give an example? make up two or three id numbers in each table, and show before/after

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    May 2002
    Posts
    28
    sorry rudy, wrong terminolgy - i mean update (not append)

    I have an empty field in my barrier table: Main_id. I would like to add to this empty field the records from the main_id field in the main table that related to the barriers table. (main_id records 11709-13847)


    This is a little example of how I would like it to look in the end:


    Barrier_id Main_id

    1 11709
    2 11710
    3 11711

    ...2139 ...13847


    etc.

    At the moment the main_id field is blank.

    I hope this makes my problem clearer.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    somewhat clearer

    which barrier ids should main_id records 11709-13847 get in the barrier table?

    if you are trying to update some barrier ids that have a null main_id, which main_ids should they get?

    see? must be me

  5. #5
    Join Date
    May 2002
    Posts
    28
    Answers to your questions:

    which barrier ids should main_id records 11709-13847 get in the barrier table? BARRIER_ID 1-2139

    if you are trying to update some barrier ids that have a null main_id, which main_ids should they get? NO, THERE ARE NO NULL VALUES

    ***************

    Thanks for responding. I dont mean to be confusing


    I would like the final barrier table to look like this:



    Barrier id Main_id

    1 11709
    2 11710
    3 11711
    4 11712



    I wanted the main_id's added so the main_id 11709 is in the same row as barrier_id 1 etc.

    At the moment the main_id field in the barrier's table is blank. I want to update these blank records with records 11709-13847 from the Main_id field in the main table.

    The tables are related through the main_id field. Main_ID field is in the Barriers table .

    Any clearer? I believe i need to an update query but am unfamiliar with the syntax required to create the query to get the results I want.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope, no clearer

    you say "NO, THERE ARE NO NULL VALUES" and then later on you say "At the moment the main_id field in the barrier's table is blank"

    blank, null, makes no difference -- if it isn't there, how can you match it to a row in main?

    how do you know which main_id barrier 1932 is supposed to have?

    are these just sequential numbers you want assigned?

    perhaps you could show the before and after tables, not just the after?

    anybody else, feel free to jump in here, i am still lost as to what sue wants

  7. #7
    Join Date
    May 2002
    Posts
    28
    are these just sequential numbers you want assigned? Yes, basically. I could type them in one by one, starting at 11709 and going till 13847 - but that probably wouldn't be alot of fun. Is there a less time consuming way to do this?

    I will trying explaining my problem from a different angle again in the morning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and all along i thought the rows had to match somehow

    update barrier
    set main_id = barrier_id + 11708
    where main_id = "" or main_id is null

    this will assign main_ids to all barrier_ids that don't already have them

    notice this could result in breaks in the sequence

    if this isn't right, please show before and after examples


    rudy

  9. #9
    Join Date
    May 2002
    Posts
    28

    thanks rudy

    i am going the try an easier update i need to do before I tackle this one.

  10. #10
    Join Date
    May 2002
    Posts
    28

    problem with sql update statement

    Hi, I would like the update the blank records in a field, but with sequential numbers, from 13-2141. (ie: the first blank record would be updated with the number 13 and the last blank record with the number 2141)

    How can I change the following sql stament to get the results I want:

    The amount of blank records is the exact amount of records i want to update. (2139)



    UPDATE FISHSRY_SCCT_MAIN SET FISHSRY_SCCT_MAIN.Meta_data_ID = "meta_data_id +12"
    WHERE (((FISHSRY_SCCT_MAIN.Meta_data_ID)=0 Or (FISHSRY_SCCT_MAIN.Meta_data_ID) Is Null));


    Thanks again for your time

    Sue

Posting Permissions

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