SQL update statement

[siamandm]

Hi
if i have a table called tblEmployee with these fields
ID autonumber , PK
EmployeeCode int
Salary int
JobTitle short text


now if have another table with the same fields but with new and updated information,
how to write a sql update statement in order to update the original table with new salary and job title?


Regrads

[HansV]

Can we match the old and new table on ID? Or on EmployeeCode?

[siamandm]

the old and new table should match on employeeCode
so employeecode=0001 in the old table is same as the employeecode=0001 in the new table

regards

[HansV]

Try this - let me know if you get an error:

UPDATE tblEmployee INNER JOIN tblOther ON tblEmployee.EmployeeCode=tblOther.EmployeeCode SET tblEmployee.Salary=tblOther.Salary, tblEmployee.JobTitle=tblOther.JobTitle

where tblOther is the name of the table with the updated information.

[siamandm]

so the old and the updated table should have an inner join relationship!
thanks a lot, i will try this

[HansV]

You only need to specify the join in the query.

[siamandm]

Thanks a lot very helpful

Regards

[siamandm]

Try this - let me know if you get an error:

UPDATE tblEmployee INNER JOIN tblOther ON tblEmployee.EmployeeCode=tblOther.EmployeeCode SET tblEmployee.Salary=tblOther.Salary, tblEmployee.JobTitle=tblOther.JobTitle

where tblOther is the name of the table with the updated information.

if I want to update the old table based on two criteria like this sample below, how do it please

Capture.PNG

many thanks

[HansV]

That would be

UPDATE NewData INNER JOIN OldData ON NewData.EmployeeCode=OldData.EmployeeCode AND NewData.Item_Code=OldData.Item_Code SET OldData.Qty=NewData.Qty

Please test on a copy of the database first.

[siamandm]

thanks a lot you are star :)