Solved

update with join

Posted on 2012-03-12
8
340 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

773 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