Re: [Koha] SQL help - Bulk Card Number modification
Dear Satish, With the help of sql query you will resolve your problem. Make your data in excel sheet then use below query for the same and resolve your query in bulk e.g. update borrowers set cardnumber = '4GH17ME18' where cardnumber = '17ME01'; Regards, ============================================================================ =========== Ramakant Senior Library Information Assistant P K Kelkar Library Indian Institute of Technology Kanpur - 208 016 Email - ramakant@iitk.ac.in, ramakant.iitk@gmail.com Phone: 0512 6797707 (O), +91 8090049089 (Mo.)
Dear Ramakant, Yes, I did the same way for modification of a single cardnumber. but how to do it for bulk? and also data is in excel. with thanks satish
Hi Satish, maybe this piece of SQL code could help you... Please adjust the parameters @prefix and @offset. In the case you're satisfied with results, use the formulas in your UPDATE command. Regards, Radek select * FROM ( SELECT @prefix:="4GH" as cardnumber, @offset:=17 as university_id ) as vars where 0 = 1 union select cardnumber, CONCAT( @prefix, left(cardnumber, 2), MID(cardnumber, 3, 2), @offset + CAST(right(cardnumber, 2) as UNSIGNED INT) ) as university_id from borrowers Dne 19.01.18 v 7:21 SATISH napsal(a):
Dear Ramakant,
Yes, I did the same way for modification of a single cardnumber. but how to do it for bulk? and also data is in excel.
with thanks satish _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
participants (3)
-
Radek Šiman -
Ramakant -
SATISH