?
Solved

How to fix this error?

Posted on 2011-05-10
6
Medium Priority
?
185 Views
Last Modified: 2012-05-11
Msg 4145, Level 15, State 1, Line 8
An expression of non-boolean type specified in a context where a condition is expected, near 'd'.

Thank you.
update d
set d.personID  = p.personID
from dbo.Doctors d join dbo.person p
on (d.lastname = p.Employee_Last_Name and d d.First_Name = p.Employee_First_Name and d.MiddleName = p.Employee_Middle_Name)
where d.personID is null

Open in new window

0
Comment
Question by:lapucca
  • 3
  • 2
6 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 35734293
you have an extra d
try this
update d
set d.personID  = p.personID
from dbo.Doctors d join dbo.person p
on (d.lastname = p.Employee_Last_Name and d.First_Name = p.Employee_First_Name and d.MiddleName = p.Employee_Middle_Name)
where d.personID is null

Open in new window

0
 

Author Comment

by:lapucca
ID: 35734393
Hi ew,
Thank you that got rid of the error but nothing is updated where they should.  Can see why is that so?  Because I know there are 58 records in Doctors where perID is null but they're still null.  
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35734417
try this way
update Doctors
set personID = p.personID
from dbo.person p
where Doctors.lastname = p.Employee_Last_Name 
and Doctors.First_Name = p.Employee_First_Name 
and Doctors.MiddleName = p.Employee_Middle_Name
and Doctors.personID is null

Open in new window

0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 39

Expert Comment

by:appari
ID: 35734418
First check if your join is returning the records you want to update or not by executing the following select SQL.
Select * from dbo.Doctors d join dbo.person p
on (d.lastname = p.Employee_Last_Name and d.First_Name = p.Employee_First_Name and d.MiddleName = p.Employee_Middle_Name)
where d.personID is null

Open in new window


if no data is returned from the above select SQL check your joins. check datatypes and datalenghts of all the name fields. if datatype or lenghts are different then try to trim extra spaces by using ltrim(rtrim()) around the name fields.
Select * from dbo.Doctors d join dbo.person p
on (ltrim(rtrim(d.lastname)) = ltrim(rtrim(p.Employee_Last_Name)) 
and ltrim(rtrim(d.First_Name)) = ltrim(rtrim(p.Employee_First_Name)) 
and ltrim(rtrim(d.MiddleName)) = ltrim(rtrim(p.Employee_Middle_Name)))
where d.personID is null

Open in new window

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35734420
or alternatively
update Doctors
set personID = (select personID
                from dbo.person p
                where Doctors.lastname = p.Employee_Last_Name 
				and Doctors.First_Name = p.Employee_First_Name 
				and Doctors.MiddleName = p.Employee_Middle_Name)
where Doctors.personID is null

Open in new window

0
 

Author Comment

by:lapucca
ID: 35734454
Hi App,
I think you might be right about that.  Please post the same thing at ID 27030491
I would like to give you credit for your post there.  Thank you.

Hi ew,
that's odd.  I ran your last code and still comes up with records with null personID, 58 of them.  Must be something else.  I'll try again tomorrow.  If still cannot figure out then I will post a new question.  Thank you.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
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…

809 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