Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

update with join

Posted on 2012-03-12
8
Medium Priority
?
350 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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

783 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