-
SQL Update
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.
-
Oracle does not support the UPDATE {table} SET {colum=} FROM {query} construct (syntax).
Check oracle manual for correct syntax.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
sql update
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);
-
You could also try one of the two approaches below (untested). Which one performs better will depend on the data volumes and distribution.
Code:
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 );
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|