Can this be done?

[enak]

I need to update the values in one table with the values from another table. I have this SQL but I get an error:

UPDATE tblBilling LEFT JOIN tblCaseInfo ON tblBilling.CaseID = tblCaseInfo.CaseID
SET tblBilling.Matter = replicate('0', 7 - len(tblCaseInfo.FileNumber)) + tblCaseInfo.FileNumber,
tblBilling.FileNumber = tblCaseInfo.FileNumber
WHERE tblBilling.FileNumber <> tblCaseInfo.FileNumber

I get the error: "Incorrect syntax near the keyword 'LEFT'."

This SQL will return the values that I want but I don't know how to incorporate it into the update statement:

select tblCaseInfo.FileNumber,replicate('0', 7 - len(tblCaseInfo.FileNumber)) + tblCaseInfo.FileNumber as Matter
from tblbilling left join tblCaseInfo ON tblBilling.CaseID = tblCaseInfo.CaseID
WHERE tblBilling.FileNumber <> tblCaseInfo.FileNumber

Can someone please help me with this?

Thanks
enak

 

[Connatic]

It can be done, but your syntax is wrong.

Sorry don't have the time to give example now, but basically you cannot put your 'LEFT JOIN ... ON' command next to the update statement, it must come after the WHERE statement !

 

[Pattycake245]

This should be the proper syntax:

UPDATE tblBilling
SET tblBilling.Matter = replicate('0', 7 -
len(tblCaseInfo.FileNumber)) + tblCaseInfo.FileNumber,
tblBilling.FileNumber = tblCaseInfo.FileNumber
FROM tblBilling
LEFT JOIN tblCaseInfo ON tblBilling.CaseID = tblCaseInfo.CaseID
WHERE tblBilling.FileNumber <> tblCaseInfo.FileNumber

Tim