SELECT IF in mysql

Databases
1

Hi

Can I use an IF statement to insert a certain number of rows with sequencial values in mysql

Table
Email_ID, Email_Ref

I need to insert 147 rows into the table with ‘2’ in Email_ID and Email_Ref incrementing from ‘4416’ to ‘4563’

2

Hi Mandes

I’m not sure the question is clear enough. You can insert anything you want in theory, not sure where the IF statement comes in.

3

SELECT @count:= 4416;

IF (@count < 4564,

INSERT into TABLE
SET Email_ID = 2,
Email_Ref = @count:=@count+1;

)

I really need something other than the IF, something like PHP’s WHILE statement that loops until the evaluation is false.

But whatever syntax I seem to use I get errors.

4

OK I find there is a WHILE in MySQL


SELECT @count:= 4416;
WHILE @count < 4564 DO 
   INSERT into TABLE
         SET  Email_ID = 2,
                  Email_Ref = @count:=@count+1; 
END WHILE;

Still getting errors though

5

I may be wrong (looks to MySQL experts for clarification) but I think you are trying to do too many things in one go, you need to set the variable before the select statement.

Would this be of use http://stackoverflow.com/questions/1101336/sql-using-an-insert-within-a-select-statement or can you not run a while loop and achieve the same thing that way?

6

Sorry Richard, that didnt help at all, I was on a deadline so I wrote a PHP script to do it for me… but Id still like to know how I can do this from within phpmyadmin for next time. The more I read the more it seemed that I needed to create a proceedure to allow this to happen …

Anyone …

7

For anyone else trying to do the same … this worked for me


DELIMITER ;;
DROP PROCEDURE IF EXISTS dowhile;;

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 4416;
  WHILE v1 < 4564 DO
  INSERT into table
         SET  Email_ID = 2,
                 Email_Ref = v1;
    SET v1 = v1 + 1;
  END WHILE;
END;;

DELIMITER ;
CALL dowhile();
8

How many rows will there already be in the table (or is going going to start with an empty table)?

9

Its an email outbox table, so it could have been empty or had any number of rows, depending on what the system was in the process of doing at the time I needed to add these rows.