string extraction in bash/mysql count of updated rows

Question

This may be a trivial thing but I cannot find it anywhere... I have a variable,

UPDATED="-------------- UPDATE table SET column = 'value' WHERE condition = 'true' -------------- Query OK, 3 rows affected Rows matched: 0 Changed: 0 Warnings: 0 Bye"

I would like to get 3 from the variable $UPDATED. It could be anything, sometimes it is 0, and other times non zero (single digit, double digits, etc.)

How do I get that value of how many rows were updated. I found that using -vvv to the mysql query gives the above text and so I'm using it. If there are any other easier ways to get the count of rows updated, then even better.

BMW · Accepted Answer · 2014-02-10 12:21:57Z

1

Using grep

echo "${UPDATED}"| grep -Po  "[0-9]*(?= rows)"

edited Feb 10, 2014 at 12:21

answered Feb 10, 2014 at 11:21

BMW

45.6k13 gold badges105 silver badges123 bronze badges

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

3 Comments

user3275103 Over a year ago

It is throwing this error, sh: 21: Syntax error: redirection unexpected

BMW Over a year ago

ok, seems your system doesn't support <<<, I updated , can you try again?

Aleks-Daniel Jakimenko-A. Over a year ago

you have to quote variable

Incognito · Accepted Answer · 2014-02-10 11:46:11Z

0

If your concern is getting the number of rows which were affected by your UPDATE statement, then you can simply use [ROW_COUNT][1] method to get that.

UPDATE table SET column = value;
SELECT row_count() INTO variable;

In your bash script, you can do this, where affected_rows is a variable which will store the value of the command

affected_rows='`mysql -h [hostname] -u[user] -p[pass] -e UPDATE table SET column = value; SELECT row_count();`';

edited Feb 10, 2014 at 11:46

answered Feb 10, 2014 at 11:08

Incognito

3,1043 gold badges30 silver badges42 bronze badges

5 Comments

user3275103 Over a year ago

This is exactly what I want but I don't know the syntax for it. I wrote as is and it is throwing an error - undeclared variable. I'm fairly new to all this so can you please help me with the exact statements?

Incognito Over a year ago

You need to declare the variable into which you are reading the value from row_count(). So just declare the variable in your program and you would be good to go.

user3275103 Over a year ago

I'm using mysql block as follows -

$MYSQL -h $MHOST -u $MUSER -p$MPASS $i << EOFMYSQL  declare updated_count=0; UPDATE table SET colum = value;  SELECT row_count() INTO updated_count;  EOFMYSQL

But it is still giving me the error ERROR 1327 (42000) at line 4: Undeclared variable: updated_count

Incognito Over a year ago

see my edit. You don't need to know the variable name.

user3275103 Over a year ago

It is giving the entire query in echo $affected_rows as opposed to just the count.

Collectives™ on Stack Overflow

string extraction in bash/mysql count of updated rows

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related