update table from select

cthorn112

Registered User.
Local time
Today, 07:51
Joined
May 31, 2012
Messages
10
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
 
I believe the query processor is complaining that you have wrapped the SELECT in (). Try dropping those characters.

Oh oh, more is wrong, should be of syntax

UPDATE ....
SET ....
WHERE ....

After the UPDATE you put the table name you are updating
After the SET you specify each column to be updated and its new value
After the WHERE you specify what criteria the UPDATE should search for in order to operate on only the rows you are interested in changing.

You do not do JOINs in UPDATEs.
 
Last edited:
I would strongly recommend that you look up examples of UPDATE queries.

Your WHERE condition makes no sense - you need something that can be TRUE or FALSE on the rigth hand of WHERE.. Access is actually telling you that it wants to see EXISTS (if that is what you mean). Look it up.
 
Michael,
I tried taking the () out and that did not work.
I have the right steps in regards to UPDATE... SET... WHERE..
I'm using JOIN in the SELECT statement, not in the UPDATE.

spikepl,
You are right about using EXISTS. I was trying to go around it when I know I have to use it :banghead:.
Once I get it working I will post the working/proper code.
Thanks you for your help guys!

-Chris
 
Rather than using a sub-select, which Jet/ACE doesn't optimize well, use an inner join. Open the QBE and drag on both tables. Create the join. Select the columns you want to update. Change the query type to update and add the new values to the Update To: cells.
 
Pat,
You made that sound too easy lol. I never used the query wizard stuff but I will give it a shot. Thanks for the advice!
-Chris
 
spikepl,

I took your advice and looked up some EXISTS examples. 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.

Code:
SELECT * FROM edi_out
WHERE NOT EXISTS 
(SELECT * FROM edi_out WHERE F10.edi_out=ctrl_num.edi_out_new_data_distinct);
-Chris
 
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
 
To do this query as a join, you will need to use a left join and test the right-side table for null in the key field.
Code:
Select * From edi_out 
Left Join edi_out_new_data_distinct On edi_out.f10 = edi_out_new_data_distinct.ctrl_num
Where edi_out_new_data_distinct.ctrl_num Is Null;
 

Users who are viewing this thread

Back
Top Bottom