asked on

How to fix this error?

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

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lapucca

ASKER

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.

Ephraim Wangoya

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

appari

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

Ephraim Wangoya

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

lapucca

ASKER

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.