update several fields in table_a from table_b based on a text field match

[stephengrenfell]

Hello,

I want to update several fields in table_a from table_b based on a text field match (where table_a.text = table_b.text).

Does anyone know if I can do this with the SQL UPDATE command ?.

I have tried using this :

UPDATE table_a
SET x = table_b.1, y = table_b.b, z = table_2.c
FROM table_a, table_b
WHERE xx = table_b.dd ;

but it does not like the FROM statement

Any help would be appreciated.

Thanks

 

[MSAccessRookie]

Hello,

I want to update several fields in table_a from table_b based on a text field match (where table_a.text = table_b.text).

Does anyone know if I can do this with the SQL UPDATE command ?.

I have tried using this :

UPDATE table_a
SET x = table_b.1, y = table_b.b, z = table_2.c
FROM table_a, table_b
WHERE xx = table_b.dd ;

but it does not like the FROM statement

Any help would be appreciated.

Thanks

What you have written looks like it should have worked (except for notation #1). Please describe what is wrong.

1. I assume that the table_2 in the set line is a typeo, and that it should be table_b.
2. References to Columns x, y, z, and xx do not reflect Table names. I assumed that these were in table_a.
3. Consider having Column names that are more descriptive than just x, y, z, and xx.

 

[SimonB1978]

Hi,

Here is the correct syntax:

UPDATE table_a, table_b
SET table_a.x = table_b.1, table_a.y = table_b.b, table_a.z = table_b.
WHERE table_a.xx = table_b.dd ;

HTH

Simon B.

 

[stephengrenfell]

Hi,

Thanks for your quick replies.

Here is the actual code

UPDATE tblInv
SET InvPayType = "PayPal", InvPayNo = Télécharger_paypal.Numéro, InvPayAmm = Télécharger_paypal.Montant
WHERE InvNote=Télécharger_paypal.Email And InvPayTrue=False;

When I run the query it asks for the value of Télecharger_paypal.Numéro and then the value of Télécharger_paypal.Montant, so it seems there is something wrong. All the field names are valid.

I have tried adding a FROM statement like this :

UPDATE tblInv SET InvPayType = "PayPal", InvPayNo = Télécharger_paypal.Numéro, InvPayAmm = Télécharger_paypal.Montant
FROM tblInv, Télécharger_paypal
WHERE InvNote=Télécharger_paypal.Email And InvPayTrue=False;

but this is thrown out due to syntax - is FROM a valid statement ?.

Any more help appreciated.

Thanks

 

[MSAccessRookie]

Try adding the location of the table Télécharger_paypal as described Below.

[FONT=Courier New][B]UPDATE tblInv , [COLOR=red]Télécharger_paypal[/COLOR][/B][/FONT]
[FONT=Courier New][B]SET tblInv.InvPayType = "PayPal", [/B][/FONT]
[FONT=Courier New][B]    tblInv.InvPayNo = Télécharger_paypal.Numéro, [/B][/FONT]
[FONT=Courier New][B]    tblInv.InvPayAmm = Télécharger_paypal.Montant[/B][/FONT]
[FONT=Courier New][B]WHERE tblInv.InvNote=Télécharger_paypal.Email AND[/B][/FONT]
[FONT=Courier New][B]      tblInv.InvPayTrue=False;[/B][/FONT]

 

[stephengrenfell]

Hi,

Now sorted.

I now know to include the second table after the UPDATE command.

Many thanks for your help.