update table from select

[cthorn112]

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!

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

[June7]

You are running this SQL in VBA procedure?

Use the Access query designer to build the UPDATE sql then paste into VBA and modify as needed for the VBA.

The * wildcard needs to be a single field name.

Review http://www.haneng.com/asp-forum/SQL-...STS_10271.html

[cthorn112]

June7,
Thanks for replying. I'm running this sql code in access as part of a report. After doing some research I found out I need to use EXISTS. When I run the query a message box pops up asking me to enter parameter value. I just want to run the query not have this box pop up. Any idea on how to bypass this box and just execute the query? Below is my code.

SELECT * FROM edi_out WHERE NOT EXISTS  (SELECT * FROM edi_out WHERE F10.edi_out=ctrl_num.edi_out_new_data_distinct);

-Chris

[cthorn112]

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.

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