• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

Update statement

I need help with this update statement.  Can someone please tell me the correct syntax.

Thanks

UPDATE tblHousehold set lnghouseid =
(select P.lnghouseid from tblpatient as P inner join tblHousehold as H on
P.lngHouseid = H.lngHouseid where P.lngpatientid = '6505')
from tblPatient as P1 inner join tblhousehold as H1 on P1.lnghouseid = H1.lnghouseid
where P1.lngPatientID = '62061'
0
running32
Asked:
running32
1 Solution
 
Brian CroweCommented:
what is the logic behind the update?  are you trying to get the HouseID from Patient 6505 or 62061?
0
 
rafranciscoCommented:
Try this:

UPDATE A
SET lnghouseid = C.lnghouseid
FROM tblHousehold A INNER JOIN tblPatient B  ON A.lnghouseid = B.lnghouseid AND B.lngPatientID = '62061', tblPatient C
WHERE C.lngPatientID = '6505'
0
 
running32Author Commented:
I got that wrong what I need to do is update all lnghouseid fields to the same lnghouseid as 62061 in tblPatient.  where lnghouseid =  6505
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
running32Author Commented:
UPDATE tblPatient set lnghouseid =
(select lnghouseid from tblpatient where lngpatientid = '21644')
where lngPatientID = '19281'

I was making it too hard.  not that this works but it's more what I am looking for
0
 
rafranciscoCommented:
UPDATE A
set lnghouseid = B.lnghouseid
FROM tblPatient A, tblPatient B
where A.lngPatientID = '19281' AND B.lngpatientid = '21644'
0
 
jrb1Commented:
Why didn't this work:

UPDATE tblPatient set lnghouseid =
(select lnghouseid from tblpatient where lngpatientid = '21644')
where lngPatientID = '19281'

Is there more than one matching row on tblpatient?  If so, you can do this:

UPDATE tblPatient set lnghouseid =
(select min(lnghouseid) from tblpatient where lngpatientid = '21644')
where lngPatientID = '19281'

or something similar
0
 
running32Author Commented:
Thanks rafrancisco as usual...... :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now