Fehler #483
Upgrade-Skript: delete_cvars_on_trans_deletion_add_shipto löscht alle custom_variables
Status:
Gelöst
Priority:
Sofort
Assignee:
-
Target version:
-
Start date:
08/09/2021
Due date:
% Done:
0%
Estimated time:
Description
Vorher:
# select count(*) from custom_variables; count -------- 669451
Eigentlich sollten nur die weg:
- (SELECT cv.id FROM custom_variables cv LEFT JOIN custom_variable_configs cvc ON (cv.config_id = cvc.id)
- WHERE module LIKE 'ShipTo'
- AND NOT EXISTS (SELECT shipto_id FROM shipto WHERE shipto_id = cv.trans_id));
(688 rows)
In Kombination mit dem DELETE werden aber alle Einträge gelöscht:
- DELETE FROM custom_variables WHERE EXISTS
- (SELECT cv.id FROM custom_variables cv LEFT JOIN custom_variable_configs cvc ON (cv.config_id = cvc.id)
- WHERE module LIKE 'ShipTo'
- AND NOT EXISTS (SELECT shipto_id FROM shipto WHERE shipto_id = cv.trans_id));
DELETE 669451
WHERE EXISTS is nur ein Boolean-Wert, d.h. sollte irgendein verwaister ShipTo Eintrag vorhanden sein, entferne einfache alle BDVs
S.a.:
https://www.postgresql.org/docs/8.1/functions-subquery.html
History
Updated by Jan Büren 5 months ago
Damit passt es:
select count (*) from custom_variables where id in (SELECT cv.id FROM custom_variables cv LEFT JOIN custom_variable_configs cvc ON (cv.config_id = cvc.id)
WHERE module LIKE 'ShipTo'
AND NOT EXISTS (SELECT shipto_id FROM shipto WHERE shipto_id = cv.trans_id));