UPDATE EMRAppointmentDetails
SET TP_PKG_ID = b.TP_PKG_ID
FROM EMRAppointmentDetails a,
  (SELECT EAH.TP_PKG_ID,
    EA.APPOINTMENT_ID
  FROM EMRAppointmentDetails EA
  INNER JOIN EMRAppointmentDetailsHistory EAH
  ON EA.APPOINTMENT_ID           =EAH.APPOINTMENT_ID
  WHERE EA.aPPOINTMENT_STATUS_ID =3
  AND EAH.aPPOINTMENT_STATUS_ID  = 8
  AND EAH.TP_PKG_ID             IS NOT NULL
  AND EA.TP_PKG_ID              IS NULL
  AND EA.HEALTH_PKG_ID          IS NOT NULL
  ) b
WHERE b.APPOINTMENT_ID = a.APPOINTMENT_ID
GO

quote:

---

Originally posted by jcelko

Since the data element name APPOINTMENT_STATUS_ID is absurd, I assume you meant apointment_status. Since identifiers cannot be NULL, why are you checking that. The proprietary UPDATE with query is non-deterministic, and needless now that DB2, Oracle, SQL Server and other SQLs have MERGE:

MERGE INTO EMRAppointmentDetails
USING (SELECT EAH.tp_pkg_id, EA.appointment_id
FROM EMRAppointmentDetailsHistory AS EAH
WHERE EAH.appointment_status = 8
AND EAH.tp_pkg_id IS NOT NULL) AS H1
ON EA.appointment_status = 3
AND EA.health_pkg_id IS NOT NULL
AND EA.tp_pkg_id IS NULL
AND EA.appointment_id = EAH.appointment_id
WHEN MATCHED
THEN UPDATE
SET EMRAppointmentDetails.tp_pkg_id = H1.tp_pkg_id;


--CELKO--
Joe Celko, SQL Guru

---