Update Query not executing (sql language included)

[basmster]

I'm trying to update two fields in the [CorporateRetailerTbl] table, [Parent Company] and [Organization Name (DBA)]. I ONLY want these fields updated if Parent Company is Null. I want to update these fields to the value in [Organization Name (DBA)] in the [2015 Master Reg List] table. Here's the SQL:






UPDATE [2015 Master Reg List]
INNER JOIN CorporateRetailerTbl ON [2015 Master Reg List].[ID] = CorporateRetailerTbl.[ID]
SET CorporateRetailerTbl.[Parent Company] = [2015 Master Reg List].[Organization Name (DBA)], CorporateRetailerTbl.[Organization Name (DBA)] = [2015 Master Reg List].[Organization Name (DBA)]
WHERE (((CorporateRetailerTbl.[Parent Company]) Is Null));



I'm only getting the null fields and nothing is filling them. Also, sidenote...the Organization Name field in the CorporateRetailer table previously had the right data in it. But I ran an Update query and that field's records were updated with the empty/null values from the Parent Company field. The opposite of what I wanted to do.

Thanks for your help.

Basmster

[June7]

First do a SELECT query to see if the records are correctly retrieved based on the JOIN and the WHERE.


Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.

[basmster]

I did and the right records are retrieved. All of the null or blanks show in the datasheet view but then when I change it to an update query...they're still blank? I did an update query earlier and it worked fine..I don't know why I'm not getting it now.

[June7]

I don't see anything wrong with the posted SQL.

I would have to review your database. If you want to provide, follow instructions at bottom of my post.

[CJ_London]

are you actually running the update or just viewing the 'result' in the query window. The view will be of the records before the update is actually run, not what they will be updated to

[basmster]

Good question Ajax. I did view it first and I tried it two ways because I always forget how the update query works. I switch the fields I put in "Field" and "Update To", viewed it both ways and then ran the update BOTH ways and both times instead of updating the fields with the correct values, it updated the wrong field with the null values! That's what I'm confused about. How could doing the update query two different ways produce the same result?

[CJ_London]

don't know. the code you posted originally will update the CorporateRetailerTbl table

[basmster]

I'm trying to attach the accdb but I don't think it worked. Maybe the file is too big? The upload got to 100% but then nothing seemed to attach?

[basmster]

Hey all. I figured out what I was doing wrong. I had to include the same Parent field twice--once for the filter condition and twice for the update to field. Thank you all for your help.