How about this.
update agreementitems
set new_iagreementid = agreements.new_iagreementid
from agreementitems as ag inner join agreements on ag.iagreementid = agreements.iagreementid
>That's what I had first but got a syntax error at "inner".
>
>I've got it to work using this:
>
>update agreementitems
>set new_iagreementid = agreements.new_iagreementid
>from agreements where agreementitems.iagreementid = agreements.iagreementid
>
>but I would love to know if the other way works or not
>
>
>>Try
>>update agreementitems
>> set new_iagreementid = agreements.new_iagreementid
>> inner join agreements on agreementitems.iagreementid = agreements.iagreementid
>>
>>
>>>
>>>I'm using PostgreSQL and trying to run the following update:
>>>
>>>update agreementitems
>>>set agreementitems.new_iagreementid = agreements.new_iagreementid
>>>from agreementitems inner join agreements on agreementitems.iagreementid = agreements.iagreementid
>>>
>>>however, I get this error:
>>>
>>>ERROR: table name "agreementitems" specified more than once
>>>
>>>This syntax works in VFP (and I think in SQL Server). Can anyone suggest the syntax that PostgreSQL uses?