Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Updating witha Left join in SQL

Posted on 2009-05-01
6
Medium Priority
?
252 Views
Last Modified: 2012-05-06
How do you do an update query in MS SQL when a left join is used?

I know this is close, but could anybody help me?

Update dbo.accountbase
Set accountbase.[primarycontactid] = dbo.contactbase.[accountid]
Left Join dbo.contactbase
on dbo.accountbase.[accountid] = dbo.contactbase.[accountid]
where accountbase.[primarycontactid] is null

Thanks,

Dave
0
Comment
Question by:Dave_Angel_Portsmouth
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 24278318
There is no point in doing an update with a left join unless you want to set values to null where a record in the left joined table does not exist..

Just change the left to inner join, think it should work from what I understand of your post..
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 24278319
what happens when you run this?
0
 
LVL 2

Author Comment

by:Dave_Angel_Portsmouth
ID: 24278344
Changing to inner join:

Update dbo.accountbase
Set accountbase.[primarycontactid] = dbo.contactbase.[accountid]
Inner Join [dbo.contactbase]
on dbo.accountbase.[accountid] = dbo.contactbase.[accountid]
where accountbase.[primarycontactid] is null

Gives me:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Inner'.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 24278357
You are missing the FROM clause.. Try slightly changed code below (using short alias names) -
Update AB
Set [primarycontactid] = CB.[accountid]
From dbo.accountbase AB
Inner Join [dbo.contactbase] CB
on AB.[accountid] = CB.[accountid]
where AB.[primarycontactid] is null

Open in new window

0
 
LVL 2

Author Comment

by:Dave_Angel_Portsmouth
ID: 24278358
Mis-typed that, was supposed to read


use NxteraCRM_MSCRM
Update dbo.accountbase
Set accountbase.[primarycontactid] = dbo.contactbase.[accountid]
Inner Join on dbo.accountbase.[accountid] = dbo.contactbase.[accountid]
where accountbase.[primarycontactid] is null
0
 
LVL 2

Author Closing Comment

by:Dave_Angel_Portsmouth
ID: 31576845
Thats great, thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 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