Btukfyl

UPDATE: I am using a query from a response below. When running this query, it is clearing the SERVICE_DATE_OUT field if it does not find a match with the where conditions. Is there a work around for this?

UPDATE PMEQMT P

       SET SERVICE_DATE_OUT = (SELECT MAX(TL.TRANS_DATE)

                        FROM TRANSLOG TL

                        WHERE P.EQNO = TL.ITEMNO AND 

                              TL.LOC_DESC = 'E-OUT OF SERVICE' AND

                              TL.TRANS_DATE >= SYSDATE - 1

                       )

WHERE P.CLASS = 'TL'

What I am trying to accomplish here is that when an ITEMNO on the TRANSLOG table is in an E-OUT OF SERVICE/E-IN SERVICE location and matches PMEQMT.EQNO then update the SERVICE_DATE_OUT/IN for that respective PMEQMT.EQNO.

The queries are updating SERVICE_DATE_OUT/IN for all records in the PMEQMT table. Can someone help me modify my queries to update only the matched PMEQMT.EQNO to TRANSLOG.ITEMNO?

UPDATE  PMEQMT

SET     SERVICE_DATE_OUT = (SELECT B.TRANS_DATE

                       FROM TRANSLOG B, PMEQMT A

                       WHERE A.EQNO = B.ITEMNO AND

                       A.CLASS = 'TL' AND

                       B.LOC_DESC = 'E-OUT OF SERVICE' AND

                       B.TRANS_DATE >= SYSDATE - 1)



UPDATE  PMEQMT

SET     SERVICE_DATE_IN = (SELECT B.TRANS_DATE

                       FROM TRANSLOG B, PMEQMT A

                       WHERE A.EQNO = B.ITEMNO AND

                       A.CLASS = 'TL' AND

                       B.LOC_DESC = 'E-IN SERVICE' AND

                       B.TRANS_DATE >= SYSDATE - 1)

I think you just want a correlated subquery:

UPDATE PMEQMT p

    SET SERVICE_DATE_OUT = (SELECT tl.TRANS_DATE

                            FROM TRANSLOG tl

                            WHERE p.EQNO = tl.ITEMNO AND

                                  p.CLASS = 'TL' AND

                                  tl.LOC_DESC = 'E-OUT OF SERVICE' AND

                                  tl.TRANS_DATE >= SYSDATE - 1

                           );

I don't think the logic quite right -- the subquery is filtering on the outer table and it could return more than one row. If I were to speculate, this is closer to what you want:

UPDATE PMEQMT p

    SET SERVICE_DATE_OUT = (SELECT MIN(tl.TRANS_DATE)

                            FROM TRANSLOG tl

                            WHERE p.EQNO = tl.ITEMNO 

                                  tl.LOC_DESC = 'E-OUT OF SERVICE' AND

                                  tl.TRANS_DATE >= SYSDATE - 1

                           )

    WHERE p.CLASS = 'TL';