Link to home
Start Free TrialLog in
Avatar of ac_davis2002
ac_davis2002Flag for United Kingdom of Great Britain and Northern Ireland

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.contractid
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
Avatar of lee555J5
lee555J5
Flag of United States of America image

UPDATE ContractExtensionBase ceb INNER JOIN ContractBase cb
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
*additional WHERE conditions.
Avatar of Stuporman
Stuporman

For syntax, I always go to www.w3schools.com and go to their SQL tutorial. They have just about everything.
Avatar of ac_davis2002

ASKER

hi

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?
ASKER CERTIFIED SOLUTION
Avatar of lee555J5
lee555J5
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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;

Open in new window


Updated: see this was parallell posted and I wasn't first :)