sql - Move blob field into new table (id, blob), and replace it with id from new table -

i have blob field contains images of 1-2 mbs. want create new table has 2 fields - primary key reference id , blob, replace field holds blob field holds reference id same blob in new table.

i don't know sql well, i'm not sure if possible using sql. or need make c++/python program it?

note: i'm using sqlite database, , since don't enforce types fields, don't need create new field, can replace blob id.

you have copy blobs other table, , update old field id of new row. latter possible last_insert_rowid(), single row, have use mechanism inserts , updates step step.

this can done trigger (and dummy view, update triggers copying not update table):

create table newtable (id integer primary key, blob);  create temporary view oldtable_view select * oldtable;  create temporary trigger move_blob instead of update on oldtable_view begin     insert newtable(blob)     select blob oldtable id = old.id;      update oldtable     set blob = last_insert_rowid()     id = old.id; end;  update oldtable_view set id = id;  drop view oldtable_view;