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

    Unanswered: SOLVED: Update Query "you are about to update 0 rows"

    Hello folks,

    I'm having a tough time figuring out what went wrong. I checked my syntax, double checked my field names, checked the data and everything appears to be fine. But whenever I run my Update Query it gives me a popup that says "You are about to update 0 rows".

    Interestingly, this update query is based on one that I had done 10 minutes before and that worked.

    I'm updating more than a dozen fields at the same time. All fields are blank and this is supposed to fill in the blanks of 59000 or so rows. Maybe I'm asking to much from access? I looked in the SQL view to get the below code, maybe something is wrong? Can someone take a peak?

    My format is
    field name: fields_name
    table: ONE BIG TABLE (The table's name)
    update to: "various text" (in quotes)
    criteria: Is Null

    It does nothing. What am I doing wrong? Here's the code if you can check it out.

    RB

    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].stock_qty = "10", [ONE BIG TABLE].stock_stock_status_changed_automatically = "No", [ONE BIG TABLE].stock_use_config_backorders = "Yes", [ONE BIG TABLE].stock_use_config_manage_stock = "Yes", [ONE BIG TABLE].stock_use_config_min_sale_qty = "Yes", [ONE BIG TABLE].stock_use_config_min_qty = "Yes", [ONE BIG TABLE].stock_use_config_notify_stock_qty = "Yes", [ONE BIG TABLE].stock_backorders = "No Backorders", [ONE BIG TABLE].stock_is_in_stock = "In Stock", [ONE BIG TABLE].stock_is_qty_decimal = "No", [ONE BIG TABLE].stock_manage_stock = "No", [ONE BIG TABLE].stock_max_sale_qty = "0", [ONE BIG TABLE].stock_min_qty = "0", [ONE BIG TABLE].stock_min_sale_qty = "1", [ONE BIG TABLE].gift_message_available = "Yes", [ONE BIG TABLE].visibility = "Catalog, Search", [ONE BIG TABLE].status = "Enabled", [ONE BIG TABLE].is_recurring = "No", [ONE BIG TABLE].enable_googlecheckout = "Yes", [ONE BIG TABLE].page_layout = "2 columns with right bar", [ONE BIG TABLE].tax_class_id = "Taxable Goods", [ONE BIG TABLE].product_attribute_set = "Default", [ONE BIG TABLE].product_type = "simple", [ONE BIG TABLE].product_websites = "base", [ONE BIG TABLE].options_container = "Block after Info Column"
    WHERE ((([ONE BIG TABLE].stock_qty) Is Null) AND (([ONE BIG TABLE].stock_stock_status_changed_automatically) Is Null) AND (([ONE BIG TABLE].stock_use_config_backorders) Is Null) AND (([ONE BIG TABLE].stock_use_config_manage_stock) Is Null) AND (([ONE BIG TABLE].stock_use_config_min_sale_qty) Is Null) AND (([ONE BIG TABLE].stock_use_config_min_qty) Is Null) AND (([ONE BIG TABLE].stock_use_config_notify_stock_qty) Is Null) AND (([ONE BIG TABLE].stock_backorders) Is Null) AND (([ONE BIG TABLE].stock_is_in_stock) Is Null) AND (([ONE BIG TABLE].stock_is_qty_decimal) Is Null) AND (([ONE BIG TABLE].stock_manage_stock) Is Null) AND (([ONE BIG TABLE].stock_max_sale_qty) Is Null) AND (([ONE BIG TABLE].stock_min_qty) Is Null) AND (([ONE BIG TABLE].stock_min_sale_qty) Is Null) AND (([ONE BIG TABLE].gift_message_available) Is Null) AND (([ONE BIG TABLE].visibility) Is Null) AND (([ONE BIG TABLE].status) Is Null) AND (([ONE BIG TABLE].is_recurring) Is Null) AND (([ONE BIG TABLE].enable_googlecheckout) Is Null) AND (([ONE BIG TABLE].page_layout) Is Null) AND (([ONE BIG TABLE].tax_class_id) Is Null) AND (([ONE BIG TABLE].product_attribute_set) Is Null) AND (([ONE BIG TABLE].product_type) Is Null) AND (([ONE BIG TABLE].product_websites) Is Null) AND (([ONE BIG TABLE].options_container) Is Null));







    Code:
    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].stock_qty = "10", [ONE BIG TABLE].stock_stock_status_changed_automatically = "No", [ONE BIG TABLE].stock_use_config_backorders = "Yes", [ONE BIG TABLE].stock_use_config_manage_stock = "Yes", [ONE BIG TABLE].stock_use_config_min_sale_qty = "Yes", [ONE BIG TABLE].stock_use_config_min_qty = "Yes", [ONE BIG TABLE].stock_use_config_notify_stock_qty = "Yes", [ONE BIG TABLE].stock_backorders = "No Backorders", [ONE BIG TABLE].stock_is_in_stock = "In Stock", [ONE BIG TABLE].stock_is_qty_decimal = "No", [ONE BIG TABLE].stock_manage_stock = "No", [ONE BIG TABLE].stock_max_sale_qty = "0", [ONE BIG TABLE].stock_min_qty = "0", [ONE BIG TABLE].stock_min_sale_qty = "1", [ONE BIG TABLE].gift_message_available = "Yes", [ONE BIG TABLE].visibility = "Catalog, Search", [ONE BIG TABLE].status = "Enabled", [ONE BIG TABLE].is_recurring = "No", [ONE BIG TABLE].enable_googlecheckout = "Yes", [ONE BIG TABLE].page_layout = "2 columns with right bar", [ONE BIG TABLE].tax_class_id = "Taxable Goods", [ONE BIG TABLE].product_attribute_set = "Default", [ONE BIG TABLE].product_type = "simple", [ONE BIG TABLE].product_websites = "base", [ONE BIG TABLE].options_container = "Block after Info Column"
    WHERE ((([ONE BIG TABLE].stock_qty) Is Null) AND (([ONE BIG TABLE].stock_stock_status_changed_automatically) Is Null) AND (([ONE BIG TABLE].stock_use_config_backorders) Is Null) AND (([ONE BIG TABLE].stock_use_config_manage_stock) Is Null) AND (([ONE BIG TABLE].stock_use_config_min_sale_qty) Is Null) AND (([ONE BIG TABLE].stock_use_config_min_qty) Is Null) AND (([ONE BIG TABLE].stock_use_config_notify_stock_qty) Is Null) AND (([ONE BIG TABLE].stock_backorders) Is Null) AND (([ONE BIG TABLE].stock_is_in_stock) Is Null) AND (([ONE BIG TABLE].stock_is_qty_decimal) Is Null) AND (([ONE BIG TABLE].stock_manage_stock) Is Null) AND (([ONE BIG TABLE].stock_max_sale_qty) Is Null) AND (([ONE BIG TABLE].stock_min_qty) Is Null) AND (([ONE BIG TABLE].stock_min_sale_qty) Is Null) AND (([ONE BIG TABLE].gift_message_available) Is Null) AND (([ONE BIG TABLE].visibility) Is Null) AND (([ONE BIG TABLE].status) Is Null) AND (([ONE BIG TABLE].is_recurring) Is Null) AND (([ONE BIG TABLE].enable_googlecheckout) Is Null) AND (([ONE BIG TABLE].page_layout) Is Null) AND (([ONE BIG TABLE].tax_class_id) Is Null) AND (([ONE BIG TABLE].product_attribute_set) Is Null) AND (([ONE BIG TABLE].product_type) Is Null) AND (([ONE BIG TABLE].product_websites) Is Null) AND (([ONE BIG TABLE].options_container) Is Null));
    Last edited by RBusiness; 01-30-12 at 16:43. Reason: It was solved. Edited title.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Instead of "Is Null", try using "=""", or a zero-length string. It often works better, but I don't know why.

    Also, I'm sure you realize that the query won't update any record that has a value (even " ") in ANY of the fields.

    Sam

  3. #3
    Join Date
    Aug 2010
    Posts
    87
    With all the quotes? Like this?

    "="""

  4. #4
    Join Date
    Aug 2010
    Posts
    87
    Actually, tried it with ="" and "=""" and still get the same error message.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are you certain there are records for that set of criteria

    you don't need to specify the table name for every column
    you only need the square brackets if there is a space bertweent he column or table name
    I'd prefer to use the IsNULL function to test whether a column is NULL or not

    Code:
    UPDATE [ONE BIG TABLE] SET stock_qty = "10", stock_stock_status_changed_automatically = "No", stock_use_config_backorders = "Yes", 
    stock_use_config_manage_stock = "Yes",
    stock_use_config_min_sale_qty = "Yes",
    stock_use_config_min_qty = "Yes",
    stock_use_config_notify_stock_qty = "Yes"
    stock_backorders = "No Backorders",
    stock_is_in_stock = "In Stock",
    stock_is_qty_decimal = "No",
    stock_manage_stock = "No",
    stock_max_sale_qty = "0",
    stock_min_qty = "0",
    stock_min_sale_qty = "1"
    gift_message_available = "Yes", 
    visibility = "Catalog, Search",
    status = "Enabled",
    is_recurring = "No",
    enable_googlecheckout = "Yes",
    page_layout = "2 columns with right bar", 
    tax_class_id = "Taxable Goods", 
    product_attribute_set = "Default", 
    product_type = "simple", 
    product_websites = "base", 
    options_container = "Block after Info Column"
    WHERE isNULL(stock_qty) 
    AND isNULL(stock_stock_status_changed_automatically)
    AND isNULL(stock_use_config_backorders) 
    AND isNULL(stock_use_config_manage_stock)
    AND isNULL(stock_use_config_min_sale_qty)
    AND isNULL(stock_use_config_min_qty) Is Null)
    AND isNULL(stock_use_config_notify_stock_qty) 
    AND isNULL(stock_backorders)
    AND isNULL(stock_is_in_stock)
    AND isNULL(stock_is_qty_decimal)
    AND isNULL(stock_manage_stock)
    AND isNULL(stock_max_sale_qty)
    AND isNULL(stock_min_qty)
    AND isNULL(stock_min_sale_qty)
    AND isNULL(gift_message_available) 
    AND isNULL(visibility) 
    AND isNULL(status) 
    AND isNULL(is_recurring)
    AND isNULL(enable_googlecheckout)
    AND isNULL(page_layout) 
    AND isNULL(tax_class_id)
    AND isNULL(product_attribute_set) Is Null)
    AND isNULL(product_type) Is Null)
    AND isNULL(product_websites)
    AND isNULL(options_container);
    im suspicious of
    Code:
    stock_qty = "10",
    if its a numeric value it doens't need to be quoted, if its going into a string / text column then it shouldn't, it should go into a suitable numeric column
    Code:
    stock_stock_status_changed_automatically = "No",
    if that's a boolean column then the correct values should be true or false in place of "yes" or "no"

    try the query as a select first to see if rows are returned
    Code:
     select * from [one big table]
    WHERE isNULL(stock_qty) 
    AND isNULL(stock_stock_status_changed_automatically)
    AND isNULL(stock_use_config_backorders) 
    AND isNULL(stock_use_config_manage_stock)
    AND isNULL(stock_use_config_min_sale_qty)
    AND isNULL(stock_use_config_min_qty) Is Null)
    AND isNULL(stock_use_config_notify_stock_qty) 
    AND isNULL(stock_backorders)
    AND isNULL(stock_is_in_stock)
    AND isNULL(stock_is_qty_decimal)
    AND isNULL(stock_manage_stock)
    AND isNULL(stock_max_sale_qty)
    AND isNULL(stock_min_qty)
    AND isNULL(stock_min_sale_qty)
    AND isNULL(gift_message_available) 
    AND isNULL(visibility) 
    AND isNULL(status) 
    AND isNULL(is_recurring)
    AND isNULL(enable_googlecheckout)
    AND isNULL(page_layout) 
    AND isNULL(tax_class_id)
    AND isNULL(product_attribute_set) Is Null)
    AND isNULL(product_type) Is Null)
    AND isNULL(product_websites)
    AND isNULL(options_container);
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2010
    Posts
    87
    "are you certain there are records for that set of criteria"

    All the fields I'm adding info to are blank!

    "im suspicious of
    Code:
    stock_qty = "10","

    I tried to change it to just 10 but the query maker keeps adding them back, but realized the field is setup to be a text field. I tried to change it and 3 others to number fields and a popup says "MS Access can't change the data type" " There isn't enough disk space or memory".

    I have plenty of both!


    "try the query as a select first to see if rows are returned"
    I pasted that in and access came back with some strange popups... wait wait....


    I remember that when I did the first query, it was done. I added it to the MEGA query that has all these update queries in it. I removed the one that was done based on your inspiring words of "are you certain there are records for that set of criteria" which made me think 'what if the one that's done is making it think they are all done' and sure enough that was it.

    Even though Access says that the fields didn't get updated to Numbers instead of text, it appear they did (in design view).

    So based on your help, this is solved. Thank you very very, very, VERY VERY VERY much! You have no idea how helpful you've been!

    RB

  7. #7
    Join Date
    Aug 2010
    Posts
    87
    @Sam Landy

    And thank you too for your contribution.

    It didn't work for me but all the same, thank you! I totally appreciate it!

Posting Permissions

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