Magento/SQL Snippets

data manipulating

set canceled state if not possible through backend

identify entity_id of order.
attention(!): order-number !== entity_id
but should be somewhere around order number.
do select before update to find out entity_id
e.g. if order number is 100001042

select * from sales_flat_order where entity_id between 1040 and 1050;

now find the correct ID and update:

update sales_flat_order set state='canceled', status='canceled' where entity_id in (1041); 
update sales_flat_order_grid set status='canceled' where entity_id in (1041);

trouble-shooting (upgrade 1.3.x --> 1.8.1)

obviously the nameing for payment method bankpayment changed to banktransfer
for that reason you could not open old orders in order history, which fails with error-report.

so do: change bankpayment --> banktransfer

update sales_flat_order_payment set method = 'banktransfer' where method = 'bankpayment';

worked for me...