Search code examples
ms-accessselectsyntaxsql-updatesubquery

Updating fields with default values depending of the finded string

libelle_operation field in table tblOperation is imported from an incomplete external file. This field contains in some cases a string ("PADDOCK" in this example) corresponding to a tblTiers.id_tiers.

Other fields in table tblOperation (id_tiers, id_type_operation and id_categorie_budget) are null and should be update with the default values (libelle_tiers, id_type_operation and id_categorie_budget). Table tblTiers contains the default values for the most recurrent id_tiers ("PADDOCK" is one of them).

The structure of the database is : database structure

Dataset for table tblTiers is:

data for tbale tblTiers

Table tblOperation contains initially :

table tblOperation inital dataset

And should contain this after "PADDOCK" has be found in libelle_operation and id_tiers, libelle_tiers and id_categorie_budget set to default value accordingly to default values stored in table tablTiers.

enter image description here

What should be the correct SQL statment for achieving this result?

Solution

  • Appears a partial link is possible with id_compte. Use WHERE clause to provide additional criteria to associate records.

    UPDATE tblOperation INNER JOIN tblTiers ON tblOperation.id_compte = tblTiers.id_compte
SET tblOperation.id_tiers = tblTiers.id_tiers, tblOperation.id_type_operation = tblTiers.id_type_operation, tblOperation.id_categorie_budget = tblTiers.id_categorie_budget
WHERE libelle_operation LIKE "*" & libelle_tiers & "*";