Update using a specific record

[jkwok]

Hi,

I have a table called 'Countries' with country codes, and other related attributes.

Countries
id country_code full_name rate

I have another table, 'Consumption', that links to the Countries table by the attribute 'Country'. Country links to 'full_name'. (Not my design!)

I want to update an attribute for every record in Consumption when 'Country' has no match with 'full_name'. So for example, if Country is blank, or has a value that isn't in the Countries table.

If this should occur, I want to be able to assign a specific 'full_name' within the Countries table. I've made a row specifically for this scenario. I've been trying to use an update query but I've had no results so far.

Countries
34 - NO - No Site ID - $79.99

Any help would be greatly appreciated!

Thanks,
Jason

[JKing]

Hi Jason,

Here's some SQL that I think should satisfy your needs.

UPDATE consumption SET consumption.country = 'Site ID'
WHERE NOT Exists (SELECT countries.full_name FROM countries WHERE countries.full_name = consumption.country);
 

I think I got all the field names correct from your description, so you should be able to copy and paste without any problems.

[NeoPa]

Another way, more typical of what Access might produce for you within the design window, would be :

UPDATE [Consumption] AS tC LEFT JOIN [Countries] AS tCs
    ON tC.Country=tCs.Full_Name
SET tC.Country='No Site ID'
WHERE tCs.Full_Name Is Null

[jkwok]

Thanks guys, both responses are really helpful!

[NeoPa]

No worries. It's good to help :)