SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    this is my query with the commented code:
    Code MySQL:
    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:
    Code MySQL:
    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.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    Italy
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's possible to do this with stored procedure or similar?

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yep, that should be doable


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •