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!

johanafm

Since you assign this to 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')

it has to return exactly one value, not several. Does it?

hadoob024

Doh! That was dumb. Got it. Nope, it returns more than 1.

Hmmm... So how would I rewrite it? I feel like the query is ALMOST there.

johanafm

How about MIN(ippa.date_created) or MAX(ippa.date_created) depending on wether you want the first or last date (before 1970).
Or, if you don't care about what date it is, LIMIT 1.

Otherwise you need to find out what other constraints would limit the selected data to contain only one acceptable row per parentID

Also, do run the select statement separately first, so that you can inspect if you really get the data you need.

hadoob024

Hmmm.. That LIMIT 1 idea sounds good.

Question, how does this query match up the dates? It just seems like I'm setting the date based on this query, but I can't tell if this query knows to only write the date of the record that matches in the other table. Did that make sense (it's still early)?

johanafm

The parenthized expression in "SET field= (...)" is exactly what is retrieved to be used as updated field value. Nothing more, nothing less. In other words, run that query to see what will be used.

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'

When I think about it, I'm starting to believe that you do not have the query you want, since this is the exact same query used to also limit what rows are updated later on, and I doubt you want all those rows to get the same value.

Perhaps what you mean to do is join table A with tableB on some fields and update A using B.someValue?

hadoob024

Cool. Makes sense. Guess it's back to the drawing board.