Solved

update with join

Posted on 2012-03-12
8
345 Views
Last Modified: 2012-03-12
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
0
Comment
Question by:ac_davis2002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 13

Expert Comment

by:lee555J5
ID: 37710545
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
 
LVL 13

Expert Comment

by:lee555J5
ID: 37710550
*additional WHERE conditions.
0
 

Expert Comment

by:Stuporman
ID: 37710552
For syntax, I always go to www.w3schools.com and go to their SQL tutorial. They have just about everything.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:ac_davis2002
ID: 37710583
hi

getting the follow error

Incorrect syntax near 'ceb'.
0
 

Expert Comment

by:Stuporman
ID: 37710600
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
 
LVL 13

Accepted Solution

by:
lee555J5 earned 500 total points
ID: 37710606
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
 

Expert Comment

by:Stuporman
ID: 37710611
Ok, forget my comment. I see lee's syntax in SQL Server's documentation now.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37710640
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

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question