ac_davis2002
asked on
update with join
i have written the following select
select ceb.New_LegacyCDBID
FROM ContractExtensionBase ceb
inner join ContractBase cb
on ceb.contractid=cb.contract id
where New_LegacyCDBID is not null
and statecode = 4 and statuscode = 5
what i want to do now is update ceb.New_LegacyCDBID using the above join but not sure of syntax?
an helpers!
Thanks
select ceb.New_LegacyCDBID
FROM ContractExtensionBase ceb
inner join ContractBase cb
on ceb.contractid=cb.contract
where New_LegacyCDBID is not null
and statecode = 4 and statuscode = 5
what i want to do now is update ceb.New_LegacyCDBID using the above join but not sure of syntax?
an helpers!
Thanks
*additional WHERE conditions.
For syntax, I always go to www.w3schools.com and go to their SQL tutorial. They have just about everything.
ASKER
hi
getting the follow error
Incorrect syntax near 'ceb'.
getting the follow error
Incorrect syntax near 'ceb'.
I'm pretty sure you can't have a join clause in an update statement. I just checked both SQL Server's and Oracle's documentation and neither of them say you can. I think you just need to find a different way to identify the rows you want to update.
Are statecode and statuscode both a part of the ContractBase table?
On second examination, it looks like you are implementing table inheritance. Is that why you want to join in the update?
Are statecode and statuscode both a part of the ContractBase table?
On second examination, it looks like you are implementing table inheritance. Is that why you want to join in the update?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ok, forget my comment. I see lee's syntax in SQL Server's documentation now.
UPDATE ceb
SET ceb.New_LegacyCDBID = ??? -- TODO add your newvalue here
FROM ContractExtensionBase ceb
INNER JOIN ContractBase cb ON ceb.contractid = cb.contractid
WHERE statecode = 4 AND statuscode = 5;
Updated: see this was parallell posted and I wasn't first :)
ON ceb.contractid = cb.contractid
SET ceb.New_LegacyCDBID = ???
WHERE statecode = 4 AND statuscode = 5;
You need to replace the ??? with your data and add any addition WHERE conditions.
Lee