0

I want to write a While Loop with Events in MySQL. After searching the internet, unfortunately, i didn't find something like this. However, i don't want to write it using procedures.

CREATE DEFINER=`root`@`localhost` EVENT `try` 
ON SCHEDULE EVERY 1 DAY STARTS '2023-03-10 09:00:00.000000'
ON COMPLETION NOT PRESERVE ENABLE DO 
BEGIN
    DECLARE counter int DEFAULT 1;
    WHILE counter <= 10 DO
    INSERT INTO admin(admin.admin_username, admin.admin_password, admin.admintype_id) VALUES('aaaa','bbbb',1)
    SET counter = counter + 1;
    END WHILE
END;
Sherif O.
  • 506
  • 4
  • 15
hakankumas
  • 23
  • 7
  • So what is your problem? Do you get error from this code – RiggsFolly Mar 09 '23 at 11:27
  • Yes, of course. Error: #1064 - 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 'WHILE counter <= 10 DO INSERT INTO admin(admin.admin_username, admin.admin_' at line 6 – hakankumas Mar 09 '23 at 11:50
  • Try [Delimiters in MySQL](https://stackoverflow.com/q/10259504). – Solarflare Mar 09 '23 at 12:35
  • It's easier to build and test your code in a procedure than an event, when happy simply copy working code. – P.Salmon Mar 09 '23 at 13:21
  • Every day you want to add ten records for the same admin.admin_username, admin.admin_password,? until '2023-03-10 09:00:00.000000' seems to me it would be easier just to do it once for all days. – P.Salmon Mar 09 '23 at 13:22
  • Actually, I wanted to do something else. This was sample code. I want to print the data returned from another operation to the other table with events. – hakankumas Mar 09 '23 at 13:53
  • I wrote code like this in procedures: BEGIN DECLARE i INT DEFAULT 1; WHILE (i <= (SELECT COUNT(*) FROM kullanici_cocuk WHERE SUBSTRING(kullanici_cocuk.cocuk_dogum_tarihi, 6, 5) = SUBSTRING(CURDATE(), 6, 5)) ) DO INSERT INTO admintouser_cocuk(admintouser_cocuk.kullanici_cocuk_id, admintouser_cocuk.admintouser_cocuk_mesaj) – hakankumas Mar 09 '23 at 13:59
  • VALUES ((SELECT kullanici_cocuk.kullanici_cocuk_id FROM kullanici_cocuk WHERE SUBSTRING(kullanici_cocuk.cocuk_dogum_tarihi, 6, 5) = SUBSTRING(CURDATE(), 6, 5)), 'Sevgili evladınızın yeni yaşını kutlar, tüm ailenizle birlikte mutlu bir yaşam dileriz.'); SET i = i+1; END WHILE; END However, since the data returned is more than one, the transaction does not take place. – hakankumas Mar 09 '23 at 13:59
  • insert values with select is just wrong use insert..select and your code is made less readable by all those brackets need more help, add sample data. – P.Salmon Mar 09 '23 at 14:35
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Mar 09 '23 at 17:17

1 Answers1

1

Unless this is an exercise in using a while loop in an event I think the same can be achieved with and insert..select

INSERT INTO admintouser_cocuk(admintouser_cocuk.kullanici_cocuk_id, 
            admintouser_cocuk.admintouser_cocuk_mesaj)
SELECT kullanici_cocuk.kullanici_cocuk_id ,
       'Sevgili evladınızın yeni yaşını kutlar, tüm ailenizle birlikte mutlu bir yaşam dileriz.'
FROM kullanici_cocuk 
wHERE SUBSTRING(kullanici_cocuk.cocuk_dogum_tarihi, 6, 5) = SUBSTRING(CURDATE(), 6, 5)
;

And I suspect using a date functions rather than subtrings would be tidier too.

P.Salmon
  • 17,104
  • 2
  • 12
  • 19