SQL UPDATE using BASH loop

Question

We changed some usernames in our application however there are some history etc. in the database that also needs to be updated. There is a file update_users.list that has the old username and new username separated by space, 2 user names per line.

Would the below script be correct, or are there some noticeable issues with it?

#!/bin/bash

cat update_users.list | while read old_user new_user
do
sqlplus -s $user/$pass@$db > user_output.log <<EOF
UPDATE auf_kopf SET kopf_sach = '%${new_user}%' WHERE kopf_sach = '%${old_user}%';
UPDATE auf_prot SET sach_bearb = '%${new_user}%' WHERE sach_bearb = '%${old_user}%';
UPDATE auf_prot_hint SET sach_bearb = '%${new_user}%' WHERE sach_bearb = '%${old_user}%';
UPDATE ordhist SET user_code = '%${new_user}%' WHERE user_code = '%${old_user}%';
UPDATE liefer_daten SET sach_bearb = '%${new_user}%' WHERE sach_bearb = '%${old_user}%';
UPDATE shipment_head SET shipment_sach = '%${new_user}%' WHERE shipment_sach = '%${old_user}%';
UPDATE lager_bew SET lb_sachbearb = '%${new_user}%' WHERE lb_sachbearb = '%${old_user}%';
UPDATE lager_abzu SET abzu_sachbearb = '%${new_user}%' WHERE abzu_sachbearb = '%${old_user}%';
UPDATE best_kopf SET best_sach = '%${new_user}%' WHERE best_sach = '%${old_user}%';
UPDATE rcpt_header SET user_name = '%${new_user}%' WHERE user_name = '%${old_user}%';
UPDATE stat_kopf SET st_kopf_sach = '%${new_user}%' WHERE st_kopf_sach = '%${old_user}%';
UPDATE stat_mod SET stat_user = '%${new_user}%' WHERE stat_user = '%${old_user}%';
/
exit
EOF

You can add an echo in the beginning of each line. This way you will see the output without having it executed. Once you are sure it has no parsing problems, go and execute it. — fedorqui
– fedorqui, Commented Aug 7, 2013 at 11:28
Can you post an example? I receive SP2-0734: unknown command beginning "echo "UPDA..." - rest of line ignored.. — user2656114
– user2656114, Commented Aug 7, 2013 at 12:06

marderh · Accepted Answer · 2013-08-07 11:29:05Z

2

I'd write the input to SQL-scriptfile first and then execute it:

awk '{print "UPDATE TABLE usertable SET col=" $2 " WHERE col=" $1 ";"}' <input >script.sql; 
echo "/" >>script.sql;  
sqlplus user/pass @script.sql;

answered Aug 7, 2013 at 11:29

marderh

1,2567 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL UPDATE using BASH loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related