I'm working with an incomplete MySQL database, adding repetitive manual updates from an external reference. It's slow going, and I'm hoping to streamline a SELECT I've used dozens of times so far. I have a couple thousand of these ahead of me.
I've seen advice for creating PROCEDURES, but since I'm trying to "alias" a partial query, I may be asking the wrong question.
I want to condense this:
SELECT book, fulltitle, author, num, lastread, category FROM books
...to something like this:
sBk
So I type sBk WHERE book="success"; and MySQL executes:
SELECT book, fulltitle, author, num, lastread, category FROM books WHERE book="success";
In my data, most authors are undefined--and my task is to fix that. So I search my reference materials for the books by this author and then update the records.
Some books are named predictably, e.g. john1, john2, john3... Others are not, e.g. jrs2, smithj9, fourteen1, AllYourBase, etc. As a result, the commands often look like this:
SELECT book, fulltitle, author, num, lastread, group FROM books WHERE book like \
"artp1%" OR book LIKE "aod%" OR book LIKE "fatal%" OR book LIKE "outside%" OR \
book LIKE "geo-cache%" OR book LIKE "frostbite" OR book LIKE "quickb3%" OR book \
LIKE "instantcof%" OR book = "sevenmile" OR book = "uberfast";
So I hit up arrow, remove the first portion, and type author= to get this:
UPDATE books SET author="John Smith" WHERE book LIKE (everything above)";
The lookups and book/author associations are manual, and I can't change that. But there's gotta be a smarter way to execute these data updates once I get a SELECT statement that matches all the correct books.
The books table has several dozen columns, or I'd simply select *.
I could copy/paste from the prior select, but I'm sloppy with a mouse, making it just as slow as riding the delete key.
It would be neat if MySQL has a function that's basically "act on the immediately previous select. But if such a function exists, I've been unable to search the correct terminology to find it.
I tried creating a procedure but I couldn't get it to accept a partial command--and the error wasn't very descriptive--is there a way to correct what I'm doing here?
CREATE PROCEDURE sBk() BEGIN SELECT book, fulltitle, author, num, lastread, category from books END;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that \
corresponds to your MySQL server version for the right syntax to use near '' at line 1
It's easy while matching a single book: hit up arrow to the previous UPDATE command, edit, move on. However, many of these commands' length and complexity pretty much kills the advantage of simply [UpArrow] to the previous statement.
The reason for your syntax error is that you didn't set a DELIMITER, and your procedure contains a BEGIN...END block. Read https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html for details on this.
After you define the stored procedure, you can call it like this:
mysql> CALL sBk();
But that's the only way you can call it. You can't add a WHERE clause to a CALL statement. It's not an alias for the query.
You might consider a VIEW instead of a procedure.
As for your difficulty using up-arrow to edit the previous statement, you might like to use the edit or \e command in the mysql client, instead of up-arrow. This command opens a text editor such as vi (or whatever your EDITOR or VISUAL environment variable names). This gives you a lot more control over how you edit the SQL statement.