Problem with UPDATE statement

[hadoob024]

I can't seem to get my UPDATE statement to work.  I basically have records in TableA that need to be updated with a datetime value from TableB.  The ID field from TableA is the primary key for TableA and it matches up to a parent_id field in TableB.  Here's my main query to see which rows I need to update:

select ipp.id,ipp.name,ipp.caseid,ipp.approved,ipp.date_entered,ipp.approval_date,ippa.date_created as Approval_Date_True_Value
from patient_procedure ipp, patient_procedure_audit ippa
where ipp.approval_date<='1970-01-01' 
and ippa.parent_id=ipp.id
and ippa.field_name='mystatus'
and ippa.after_value_string='301750a3-3336-1c97-b94d-4a9e30e03590'
order by ipp.date_entered desc

 

 

Basically, any row in TableA (in my example it's "patient_procedure") where it's "approval_date" value is less than "1970-01-01", I want to update this datetime field with the value from TableB (in my example it's "patient_procedure_audit").  I've tried several queries, the last one being:

update ipg_patient_procedure
set approval_date=(select ippa.date_created
from ipg_patient_procedure ipp, ipg_patient_procedure_audit ippa
where ipp.approval_date<='1970-01-01' 
and ippa.parent_id=ipp.id
and ippa.field_name='mystatus'
and ippa.after_value_string='301750a3-3336-1c97-b94d-4a9e30e03590')
where exists
(select ippa.date_created
from ipg_patient_procedure ipp, ipg_patient_procedure_audit ippa
where ipp.approval_date<='1970-01-01' 
and ippa.parent_id=ipp.id
and ippa.field_name='mystatus'
and ippa.after_value_string='301750a3-3336-1c97-b94d-4a9e30e03590')

 

But this one gives me an error stating:

 

"Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated."

 

 

I'm lost.  Any thoughts?  Thanks!

[Gamic]

What you really want is something that looks like this:

 

update a
set approval_date = b.date
from <tableName> a
join <tableName> b on a.id = b.parentid
where a.approval_date <= '1 Jan 1970'
--rest of where clause

 

 

[hadoob024]

Very cool!  Thanks!!!