Update query SQL error 1

[haygordon]

In table1 I have a field "Teacher" and I want the data from row 4 to be inserted into table2 "outteacher" where the "yeargoup"= 6P

I have tried this sql code:

UPDATE Table1 SET Table1.Outteacher = Table2.teacher.[tbl2ID] = "4"
WHERE (((Table1.Yeargroup)="6P"));

Thereis an error but I can't find it - any suggestions?

 

[PCLewis]

First of all if you want to INSERT records, you will be doing an INSERT query.

INSERT INTO Table2 ( Teacher, tbl2ID )
SELECT Table1.Outteacher, 4 FROM Table1
WHERE Table1.Yeargroup="6P";

The above assumes you really have a field called tbl2ID in yout Table2 and that you really want to set that field to the value of 4

 

[haygordon]

Sorry, my use of English made it unclear.

I want to update the existing data in table1.outteacher with the data from table2.teacher

The data has to be found in table2 and copied to table1. The conditional field in table1.yeargroup

I do not want to create extra records.

Sorry if I confused things and a big thanks for your help,

Gordon

 

[PCLewis]

UPDATE Table1, Table2 SET Table1.Outteacher = Table2.teacher
WHERE Table1.Yeargroup="6P" AND tbl2ID="4";

Notes:
If Tbl2ID is not text, remove the quotation marks around the 4.
You've not specified any relationship between table1 and table2 hence this query might not do what you think.

 

[haygordon]

Thank you PCLewis for an excellent piece of help - the code ran a treat and I can now modify it to extend the updates required.

Congratulations of you Tek-Tips award, you deserve it.

Gordon