Shell script stupidity …
Earlier this week I was told that I had to add some SQL to a batch process that we run every night to correct an error. Basically, this was a stopgap measure until the programmers were able to get in new code. I was in a silly mood that night, so I got a little more creative than I usually would be.
For the sake of this example, let’s pretend that we have two tables:
create table robots (
robot_id integer auto_increment,
name varchar(20),
state char(2)
);
create table robot_parts (
robot_id integer,
part_type varchar(20)
);
These two tables are created using MySQL syntax because that’s what I’ve got at home, and that’s what most of you have to play around with also.
If the code was bad and it was inserting hands for all the robots built in Utah, even if that factory didn’t build robots with hands, we would perform a query like this to find all the affected robots:
select r.robot_id
from robots r, robot_parts rp
where r.robot_id = rp.robot_id
and r.state = ‘UT’
and rp.part_type = ‘hands’;
To delete those hands you might initially think to do a query like this:
delete from robot_parts
where robot_id in (
select r.robot_id
from robots r, robot_parts rp
where r.robot_id = rp.robot_id
and r.state = ‘UT’
and rp.part_type = ‘hands’
);
The error you’ll get in mysql is this one:
ERROR 1093 (HY000): You can’t specify target table ‘robot_parts’ for update in FROM clause
Normally what I would do is build a quick temp table, populate it with all the affected robot_id’s, and then query it in the delete statement. Which is fine. I was in a silly mood, remember? Didn’t want to deal with temp tables. In a shell script you have to drop them (for good measure), create them, populate them, delete the stuff you want to delete, and then drop them again. Too much work.
Another trick that I’ve done in the past is to write SQL that writes my SQL. Then I output that to a file and pipe the file to my SQL client. Something like this:
select concat(“delete from robot_parts where robot_id = “,r.robot_id,” and part_type = ‘hands’;”)
from robots r, robot_parts rp
where r.robot_id = rp.robot_id
and r.state = ‘UT’
and rp.part_type = ‘hands’;
It would have worked just fine. However, I was in a silly mood and didn’t want to play with temporary files any more than I wanted to play with temporary tables. So, I thought about it and wondered how difficult it would be to get it to pipe the output back into my SQL client. Something like this:
echo “select concat(\”delete from robot_parts where robot_id = \”,r.robot_id,\” and part_type = ‘hands’;\”)
from robots r, robot_parts rp
where r.robot_id = rp.robot_id
and r.state = ‘UT’
and rp.part_type = ‘hands’;” | mysql -u username -p -h mysql.domain.com database | grep -v concat | mysql -u username -p -h mysql.domain.com database
MySQL is actually a little bit friendlier than my database at work when it comes to doing this. There was only one line that I had to remove. I’m certain there’s some switch to turn off column names in MySQL, but my cursory glance of the docs didn’t find it. Besides, the “grep -v” works fine. At work each line had a “(constant)” on it, that I used the cut command to remove.
One quick note though. The “-p” switch in MySQL prompts for a password. This example will prompt you for a password twice. In an actual shell script you should use the “–password=<password>” switch for each of the calls so that it won’t prompt.
