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
ac_davis2002Asked:
Who is Participating?
 
lee555J5Connect With a Mentor Commented:
Sorry, I had Access on the brain. Try this

UPDATE ceb
SET ceb.New_LegacyCDBID = ???
FROM ContractExtensionBase ceb INNER JOIN ContractBase cb
ON ceb.contractid = cb.contractid
WHERE statecode = 4 AND statuscode = 5;
0
 
lee555J5Commented:
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
0
 
lee555J5Commented:
*additional WHERE conditions.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
StupormanCommented:
For syntax, I always go to www.w3schools.com and go to their SQL tutorial. They have just about everything.
0
 
ac_davis2002Author Commented:
hi

getting the follow error

Incorrect syntax near 'ceb'.
0
 
StupormanCommented:
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?
0
 
StupormanCommented:
Ok, forget my comment. I see lee's syntax in SQL Server's documentation now.
0
 
jogosCommented:
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 :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.