2

I can't work out how to get the mysql client to return the number of rows deleted to the shell when running a delete. Does anyone know what option will enable this? Or ways around it?

Here's what i'm trying, but i get no output:

#!/bin/bash
deleted=`mysql mydb -e "delete from mytable where insertedtime < '2010-04-01 00:00:00'"|tail -n 1`

I was expecting something like this as the output from mysql:

deleted
999999

Which is why i have the tail -n 1 so i only pick up the count and not the column name.

When running the command by hand (mysql mydb -e "delete from mytable where insertedtime < '2010-04-01 00:00:00'") there is no output. When running the command interactively when running the mysql client i ge the following: 

mysql>delete from mytable where insertedtime < '2010-04-01 00:00:00';
Query OK, 0 rows affected (0.00 sec)

I want to get the rows affected count into my shell variable.

Any help would be most appreciated.

simonlord
  • 4,347
  • 1
  • 19
  • 12
  • What output does the command give you when running it manually? (Please edit your question and provide an example of the output.) – Andy Shellam Apr 13 '10 at 09:16
  • If the query was successful, then the output will be something like `Query OK, 1 row affected (0.06 sec)` – Felix Kling Apr 13 '10 at 09:18
  • *Duplicate:* http://stackoverflow.com/questions/1083866/how-to-get-number-of-rows-affected-while-executing-mysql-query-from-bash – Felix Kling Apr 13 '10 at 09:35

3 Answers3

4

This question was asked previously: How to get number of rows affected, while executing MySQL query from bash?

Community
  • 1
  • 1
Felix Kling
  • 795,719
  • 175
  • 1,089
  • 1,143
1

add "-vv"

mysql mydb -e "delete from mytable where insertedtime < '2010-04-01 00:00:00' -vv

https://dba.stackexchange.com/questions/23527/how-can-you-output-update-insert-query-results-when-using-mysql-e-execute

Community
  • 1
  • 1
Anders B
  • 3,343
  • 1
  • 26
  • 17
0
 deleted=`mysql mydb -e "delete from mytable where insertedtime < '2010-04-01 00:00:00'"|tail -n 1`


 int icount = mysql_CountRow(deleted);

it works for me try this.

Badr
  • 10,384
  • 15
  • 70
  • 104