While loop on mysql DB

Question

First of all, sorry for my noobish question, because I'm pretty sure it is.

I'm using MySQL 5.5.53 on an Ubuntu 14.04.

I'm trying to delete all users with a username starting with the letter "z" from a database. But I also need to delete their rights and remove them from the group they belong too.

The only common point between these tables is the user_id in the user table.

Below, this is the method I'm trying to use to delete a user. Once it works for it, it'll be easy as abc for the other tables.

Unfortunately for this does not work:

I create a file "clearusers.sql":

CREATE PROCEDURE clearusers()
BEGIN
        SELECT @count := COUNT(*) FROM db_user WHERE username like 'z%';
        WHILE @count > 0 DO
                SELECT @user_id := user_id FROM db_user WHERE username LIKE 'z%' LIMIT 1;
                delete * from db_user where user_id = @user_id;
                @count = @count -1;
        END WHILE;
END;

Then I do this:

mysql -u root -p mysqldb < clearusers.sql

Then I get this error:

ERROR 1064 (42000) at line 1: 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 3

I don't know what I'm doing wrong. So to anybody : Thanks by advance for your help.

Proc.

Question edited! Sorry for not being clear enough.

Processor
– Processor

2016-12-05 13:45:47 +00:00
Commented Dec 5, 2016 at 13:45 — Processor
– Processor, Commented Dec 5, 2016 at 13:45

e4c5 · Accepted Answer · 2016-12-05 10:59:12Z

2

You don't need a stored procedure for this. What you need is one line of sql

DELETE FROM db_user WHERE user_id LIKE 'z%';

That is all. What your stored procedure is doing is iterating through all the users in your table with names beginning with z and deleting them one by one. That could mean hundreds, thousands or even millions of repeated queries depending on the size of the data.

Generally as a rule of thumb, when you find yourself looping through a result set and doing another query with those results, there is another way that does not involve loops and repeated queries.

answered Dec 5, 2016 at 10:59

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

Comments

Processor · Accepted Answer · 2016-12-06 08:39:07Z

0

So I found myself what's going wrong with my script here are the wrong and fixed version:

What was wrong in comment:

--Missing delimiter change
    CREATE PROCEDURE clearusers()
    BEGIN
            SELECT @count := COUNT(*) FROM db_user WHERE username like 'z%';
            WHILE @count > 0 DO
                    SELECT @user_id := user_id FROM db_user WHERE username LIKE 'z%' LIMIT 1;
--Star not needed for the delete
                    delete * from db_user where user_id = @user_id;
--Select missing before @count
                    @count = @count -1;
            END WHILE;
    END;
--missing back to original delimiter.
--missing call procedure.

Good:

DELIMITER çç
CREATE PROCEDURE clearusers()
BEGIN
        SELECT @count := COUNT(*) FROM db_user WHERE username like 'z%';
        WHILE @count > 0 DO
                SELECT @user_id := user_id FROM db_user WHERE username LIKE 'z%' LIMIT 1;
                DELETE FROM db_user where user_id = @user_id;
                SELECT @count := @count -1;
        END WHILE;
END;
çç

DELIMITER ;

Thanks for your help.

answered Dec 6, 2016 at 8:39

Processor

3341 gold badge3 silver badges15 bronze badges

1 Comment

e4c5 Over a year ago

which is basically doing hundreds of queries when one is sufficient. This is not the correct answer even though it's your own answer.

Collectives™ on Stack Overflow

While loop on mysql DB

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related