Professional Web Applications Themes

mysql, dreaded error 1064 - MySQL

Hi, I've new to mysql and sql in general and I'm trying to find the correct syntax to delete all rows EXCEPT the one row with the max value of a certain field. For example, my DB has: UserName Sessions Chris 2 Chris 4 Mark 4 Chris 3 Mark 2 I want to delete everything except: Chris 4 Mark 4 So for each UserName, delete everthing except the row with the most sessions. Here's what I thought would work: DELETE * from dbname as A where A.Sessions <> (select MAX(Sessions) from dbname where UserName = A.UserName); When I try this ...

  1. #1

    Default mysql, dreaded error 1064

    Hi,

    I've new to mysql and sql in general and I'm trying to find the
    correct syntax to delete all rows EXCEPT the one row with the max
    value of a certain field.

    For example, my DB has:
    UserName Sessions
    Chris 2
    Chris 4
    Mark 4
    Chris 3
    Mark 2

    I want to delete everything except:
    Chris 4
    Mark 4

    So for each UserName, delete everthing except the row with the most
    sessions.

    Here's what I thought would work:


    DELETE * from dbname as A where A.Sessions <> (select MAX(Sessions)
    from dbname where UserName = A.UserName);

    When I try this with mySql 4.1 I get:

    Error Code : 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 'select MAX(Sessions) from dbname where U
    (0 ms taken)


    Any ideas?!
    Thanks

    che10234@hotmail.com Guest

  2. #2

    Default Re: mysql, dreaded error 1064

    com wrote: 

    Use Google to search this group for the words "strawberry query" (include
    the quotes).


    Paul Guest

  3. #3

    Default Re: mysql, dreaded error 1064

    What an interesting response....I feel like I've been told some secret
    code. I found it and I'll try it out, thanks. Although I still don't
    see what is syntactically wrong with my first attempt.


    che10234@hotmail.com Guest

Similar Threads

  1. Error 1064 on 'SET PASSWORD'
    By gmoudry@gmail.com in forum MySQL
    Replies: 2
    Last Post: January 2nd, 09:03 PM
  2. ERROR 1064 in mySQL 5.0.27
    By avidfan in forum MySQL
    Replies: 2
    Last Post: December 31st, 05:55 AM
  3. ERROR 1064 when trying to load dump from sql file
    By docw@globetrotter.net in forum MySQL
    Replies: 3
    Last Post: October 22nd, 09:56 PM
  4. Replies: 2
    Last Post: February 23rd, 07:34 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139