SQL Update

[misuk11]

can anyone tell me why i get the error

'SQL command not properly ended'

this sql was lifted directly from a Sybase database and seems to work OK there


update billing_update_items
set archive_flag = p_flag
from billing_update_items, dummy_billings_dets
where billing_update_items.service_id = p_service
and billing_update_items.class_id = p_class
and billing_update_items.update_type = 1
and billing_update_items.billing_det_id = dummy_billings_dets.id
and dummy_billings_dets.txs_date >= p_start_date
and dummy_billings_dets.txs_date <= p_end_date
and archive_flag <> p_flag;
COMMIT;

tia
mark.

[LKBrwn_DBA]

Oracle does not support the UPDATE {table} SET {colum=} FROM {query} construct (syntax).
Check oracle manual (http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems52.htm#36432) for correct syntax.
;)

[misuk11]

I assume the sql could be modified as below to achieve the same result ?

update billing_update_items
set archive_flag = p_flag
WHERE billing_update_items.id IN (SELECT billing_update_items.ID
from billing_update_items, dummy_billings_dets
where billing_update_items.service_id = p_service
and billing_update_items.class_id = p_class
and billing_update_items.update_type = 1
and billing_update_items.billing_det_id = dummy_billings_dets.id
and dummy_billings_dets.txs_date >= p_start_date
and dummy_billings_dets.txs_date <= p_end_date
and archive_flag <> p_flag);

[WilliamR]

You could also try one of the two approaches below (untested). Which one performs better will depend on the data volumes and distribution.
UPDATE billing_update_items i
SET archive_flag = p_flag
WHERE service_id = p_service
AND class_id = p_class
AND update_type = 1
AND billing_det_id IN
( SELECT d.id
FROM dummy_billings_dets d
WHERE d.txs_date BETWEEN p_start_date AND p_end_date
AND d.archive_flag <> p_flag );

UPDATE billing_update_items i
SET archive_flag = p_flag
WHERE service_id = p_service
AND class_id = p_class
AND update_type = 1
AND EXISTS
( SELECT 1
FROM dummy_billings_dets d
WHERE d.id = i.billing_det_id
AND d.txs_date BETWEEN p_start_date AND p_end_date
AND d.archive_flag <> p_flag );