Complex select query with nested if constructs, query insert and query delete

Databases
1

Hi,

I have this mysql query with maybe clear comments.
Please can you help me to correct it?
I think that it is a comprehensible code but it requires many corrections.
and also forgive me for my bad english. :rolleyes:

this is my query with the commented code:

SELECT log_uname
    IF (
          log_uname = 'new_username',
          /* if true that already exists but it is not a verified username */
          (
             SELECT flag_verified, log_uname
                 /* nested if */
                 IF (
                       (TIMESTAMPDIFF(MINUTE, NOW(), SELECT date_registered FROM users WHERE flag_verified = 0 AND log_uname = 'new_username')<1440),
                        /* if true that shorter than 24 hours */
                        NULL,
                        /* if false */
                        DELETE FROM users WHERE log_uname     = 'new_username'
                        /* if possible in the false clause can I add also the following operation?) */
                        /* INSERT into users SET email           = 'email@new_domain.xxx',
                                              log_uname       = 'new_username',
                                              log_pswrd       = '123456',
                                              date_registered = NOW(),
                                              last_access     = NULL,
                                              flag_verified   = 0,
                                              access_counter  = 0 */
                    )
               FROM users
              WHERE flag_verified = 0 AND log_uname = 'new_username'
           ),
           /* if false. if select doesn't retrieve any given username, insert it as new */
           (INSERT into users SET email           = 'email@new_domain.xxx',
                                  log_uname       = 'new_username',
                                  log_pswrd       = '123456',
                                  date_registered = NOW(),
                                  last_access     = NULL,
                                  flag_verified   = 0,
                                  access_counter  = 0
           )
       )
  FROM users
 WHERE log_uname = 'new_username'

this is the same query with uncommented code if useful:

SELECT log_uname
    IF (
          log_uname = "new_username",
          (
             SELECT flag_verified, log_uname
                 IF (
                       (TIMESTAMPDIFF(MINUTE, NOW(), SELECT date_registered FROM users WHERE flag_verified = 0 AND log_uname = 'new_username')<1440),
                        NULL,
                        DELETE FROM users WHERE log_uname     = 'new_username'
                    )
               FROM users
              WHERE flag_verified = 0 AND log_uname = 'new_username'
           ),
           (
            INSERT into users SET email           = 'email@new_domain.xxx',
                                  log_uname       = 'new_username',
                                  log_pswrd       = '123456',
                                  date_registered = NOW(),
                                  last_access     = NULL,
                                  flag_verified   = 0,
                                  access_counter  = 0
           )
       )
  FROM users
 WHERE log_uname = 'new_username'

many thanks really. :slight_smile:

2

You simply can’t do this. There’s no such thing as a SELECT with nested DELETE and INSERT queries.

Run your one SELECT query. Retrieve its results into your program. Based on the content of the result set, issue your DELETE query or your INSERT query.

3

it’s possible to do this with stored procedure or similar?

4

Yep, that should be doable