Code needed to get sql queries

Shell Programming and Scripting
1

Hi

i need code to get sql queries through a shell script for a text file input which contain the service ids

iputfile

I-H-2048-10GB-M
I-H-4096-12GB-M
I-H-2048-p1000-M

the code should contain below queries among which service_id is replacable with value from input file.

UPDATE service_subscription SET quota_exceeded = 'N' WHERE service_id='I-H-2048-10GB-M';
 DELETE FROM service_subscription_override WHERE service_id='I-H-2048-10GB-M';
 commit;
 select * from service_subscription where service_id='I-H-2048-10GB-M';
 select *  FROM service_subscription_override WHERE service_id='I-H-2048-10GB-M';

Required output.

UPDATE service_subscription SET quota_exceeded = 'N' WHERE service_id='I-H-2048-10GB-M';
 DELETE FROM service_subscription_override WHERE service_id='I-H-2048-10GB-M';
 commit;
 select * from service_subscription where service_id='I-H-2048-10GB-M';
 select *  FROM service_subscription_override WHERE service_id='I-H-2048-10GB-M';
UPDATE service_subscription SET quota_exceeded = 'N' WHERE service_id='I-H-4096-12GB-M';
 DELETE FROM service_subscription_override WHERE service_id='I-H-4096-12GB-M';
 commit;
 select * from service_subscription where service_id='I-H-4096-12GB-M';
 select *  FROM service_subscription_override WHERE service_id='I-H-4096-12GB-M';
UPDATE service_subscription SET quota_exceeded = 'N' WHERE service_id='I-H-2048-p1000-M';
 DELETE FROM service_subscription_override WHERE service_id='I-H-2048-p1000-M';
 commit;
 select * from service_subscription where service_id='I-H-2048-p1000-M';
 select *  FROM service_subscription_override WHERE service_id='I-H-2048-p1000-M';

can anybody help.

tnx in advance.

2

I am providing you a sample output , apply the same logic to other rows as well.

$ cat file
I-H-2048-10GB-M
I-H-4096-12GB-M
I-H-2048-p1000-M

for id in `cat file`
do
echo "UPDATE service_subscription SET quota_exceeded = 'N' WHERE service_id='$id';"  >> SQL_file
---Here add more SQL commands as per your requirement.
done
1 Like
3

Hi

tnx ,the code is working fine.

4

You can also try this.

cat vikram3.txt
I-H-2048-10GB-M
I-H-4096-12GB-M
I-H-2048-p1000-M

#!/bin/sh
while read i
do
echo "UPDATE service_subscription SET quota_exceeded = 'N' WHERE service_id='$i';" >> SQL_FILE
done <  vikram3.txt
5

This link will give you other options as well :slight_smile: