Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    7

    Unanswered: update table from select

    Hi All,

    I'm trying to update two columns (f13, f14) table based on a select statement. If I run the select statement on its own I get the desired results. Now I need to update those records. I get this error message when trying to run the code below. "You have written a subquery that can return more than one field without using the exists reserved word in the main query's FROM clause.Revise the SELECT statement of the subquery to request only one filed.". Should I be taking a different approach? Please feel free to comment. Thanks in advance!

    Code:
    UPDATE edi_out SET f13='Yes' AND f14='same day duplicate'
    WHERE 
    (SELECT * FROM edi_out A 
    LEFT JOIN edi_out_new_data_distinct B ON A.f10=B.ctrl_num AND A.f3=B.f3 AND A.f6=B.f6
    WHERE B.ctrl_num IS NULL);
    -Chris

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Is there anything that uniquely defines a row in edi_out?

    Right now you have two issues:
    1) You don't have anything in the where clause to compare the results of the select against
    2) The select is returning too many fields (all the fields from both edi_out and out_new_data_distinct). It should only return whatever field is in your where clause

    something like:
    Code:
    UPDATE edi_out SET f13='Yes' AND f14='same day duplicate'
    WHERE f10 in (SELECT A.f10 FROM edi_out A 
    LEFT JOIN edi_out_new_data_distinct B ON A.f10=B.ctrl_num AND A.f3=B.f3 AND A.f6=B.f6
    WHERE B.ctrl_num IS NULL);

    Steve

  3. #3
    Join Date
    May 2012
    Posts
    7
    Steve,
    A combination of fields makes a unique record. It's not just based on one field. This is a temp table that will later get imported into a bigger table that has an auto increment field.
    1)Forgive if my approach is crude. I just want to update the values of the results. I'm not looking to compare the results against anything.
    2) I see what you are saying, its returning all the fields. I should change the * to only the fields I want to return.
    Thank you for your input!

    -Chris

  4. #4
    Join Date
    May 2012
    Posts
    7
    I figured out my problem. Access was complaining because I was not telling it where the field was coming from. Here is the correct query.
    Code:
    SELECT * FROM edi_out A WHERE NOT EXISTS (SELECT * FROM edi_out_new_data_distinct WHERE edi_out.f10=edi_out_new_data_distinct.ctrl_num);
    -Chris

Posting Permissions

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