Update with Where Exists problem

3 Aug 2006 8:35 PM

Phill

I'm running the following query to update some rows. The problem is it is
updating rows that are not met in the Where Exists clause. Does anyone see
where I've gone wrong? Thanks.

UPDATE OUPT_PRD.REQUEST_LINE
SET OUPT_PRD.REQUEST_LINE.STATUS = 'FO'
WHERE EXISTS
(SELECT DISTINCT OUPT_PRD.REQUEST_LINE.REQUEST_ID
FROM OUPT_PRD.REQUEST_LINE, OUPT_PRD.REQ_LINE_DETAIL
WHERE
OUPT_PRD.REQ_LINE_DETAIL.PARENT_RMA_ID is Null
AND OUPT_PRD.REQUEST_LINE.REQUEST_PROCESS = '3RR'
AND OUPT_PRD.REQUEST_LINE.STATUS = 'OP'
AND OUPT_PRD.REQUEST_LINE.place_id_to_shp = 'TIM'
AND OUPT_PRD.REQUEST_LINE.REQUEST_ID = OUPT_PRD.REQ_LINE_DETAIL.REQUEST_ID
AND OUPT_PRD.REQUEST_LINE.REQUEST_LINE =
OUPT_PRD.REQ_LINE_DETAIL.REQUEST_LINE)

3 Aug 2006 8:47 PM

Alexander Kuznetsov

Your EXISTS clause is not correlated, so it's either updating all the
rows or nothing. Easy to fix though:

UPDATE OUPT_PRD.REQUEST_LINE
SET OUPT_PRD.REQUEST_LINE.STATUS = 'FO'
WHERE EXISTS
(SELECT DISTINCT OUPT_PRD.REQUEST_LINE.REQUEST_ID
--FROM OUPT_PRD.REQUEST_LINE, OUPT_PRD.REQ_LINE_DETAIL
FROM OUPT_PRD.REQ_LINE_DETAIL
WHERE
OUPT_PRD.REQ_LINE_DETAIL.PARENT_RMA_ID is Null
AND OUPT_PRD.REQUEST_LINE.REQUEST_PROCESS = '3RR'
AND OUPT_PRD.REQUEST_LINE.STATUS = 'OP'
AND OUPT_PRD.REQUEST_LINE.place_id_to_shp = 'TIM'
AND OUPT_PRD.REQUEST_LINE.REQUEST_ID =
OUPT_PRD.REQ_LINE_DETAIL.REQUEST_ID
AND OUPT_PRD.REQUEST_LINE.REQUEST_LINE =
OUPT_PRD.REQ_LINE_DETAIL.REQUEST_LINE)

3 Aug 2006 9:57 PM

Phill

Thanks. Do I need the Select distinct or can I say Select 0

Show quote

"Phill" wrote:

> I'm running the following query to update some rows. The problem is it is
> updating rows that are not met in the Where Exists clause. Does anyone see
> where I've gone wrong? Thanks.
>
> UPDATE OUPT_PRD.REQUEST_LINE
> SET OUPT_PRD.REQUEST_LINE.STATUS = 'FO'
> WHERE EXISTS
> (SELECT DISTINCT OUPT_PRD.REQUEST_LINE.REQUEST_ID
> FROM OUPT_PRD.REQUEST_LINE, OUPT_PRD.REQ_LINE_DETAIL
> WHERE
> OUPT_PRD.REQ_LINE_DETAIL.PARENT_RMA_ID is Null
> AND OUPT_PRD.REQUEST_LINE.REQUEST_PROCESS = '3RR'
> AND OUPT_PRD.REQUEST_LINE.STATUS = 'OP'
> AND OUPT_PRD.REQUEST_LINE.place_id_to_shp = 'TIM'
> AND OUPT_PRD.REQUEST_LINE.REQUEST_ID = OUPT_PRD.REQ_LINE_DETAIL.REQUEST_ID
> AND OUPT_PRD.REQUEST_LINE.REQUEST_LINE =
> OUPT_PRD.REQ_LINE_DETAIL.REQUEST_LINE)