Jump to content


  • Posts

  • Joined

  • Last visited


Posts posted by bubblegum.anarchy

  1. The query is designed for MySQL and not Oracle - the error described above is an Oracle error.


    The `as occurs` should following `count(*)` as an alias for what every the count value is to be used in the HAVING clause.


    There are four function calls in play with the following (starting from the inner most and using the programme_name example `Alvin And The Chipmunks Meet The Wolfman`):


    GROUP BY SOUNDEX(replace(replace(lower(programme_name), ' the', ''), ' and', ''))


    lower('Alvin And The Chipmunks Meet The Wolfman') converts the string to lower case resulting in 'alvin and the chipmunks meet the wolfman'


    replace('alvin and the chipmunks meet the wolfman', ' the', '') replaces all occurances of ' the' with an empty string and results in 'alvin and chipmunks meet wolfman'


    replace('alvin and chipmunks meet wolfman') replaces all occurances of ' and' with an empty string and results in 'alvin chipmunks meet wolfman'


    soundex('alvin and chipmunks meet wolfman') returns the string into a soundex (sounds like) character string that ignores not alphabtic characters - words like pair and pare return the same string and mayor and mare also return the same string.

  2. Here is an option with two seperate queries (MAKE A BACKUP FIRST)


    The first creates a string that contains a set of all the unique ids and the second removes all the record id's that do not match have a match in the set - this way each unique copy is still kept. (IMPORTANT: all the records will be removed if @ids is empty when when the DELETE query is executed)


    SELECT group_concat(id SEPARATOR ',') AS ids FROM (
    SELECT id FROM creature group by concat_ws(',', position_x, position_y, position_z)
    ) AS derived INTO @ids
    DELETE FROM creature WHERE NOT find_in_set(id, @ids);

  3. 1. open vim

    2. open the text file

    3. type : (typing the colon should drop vim's focus the command line)

    4. type %s/^\([0-9]*\)\s\(.*\)/('\1','\2'),/g

    5. hit the enter key

    5. swap the last comma in the text file with a semicolon

    6. add the INSERT statement to the front of the entire string


    .... and and you have yourself a SQL query.

  4. yea it is large. 80,000 products. I just took that default and function off for now. I don't need to add any more products I just need to move it into my mysql database so I can use it with my php shopping basket.




    If you are every required to add new products an INSERT INTO `CommerceInsight_dbo`.`Item` SET `rowguid` = uuid() will probably be appropriate.

  5. I suppose so.. but I am not highly experienced with csv import/exporting.


    Besides, opening the file in vim and executing the regexp:




    then replacing the last comma with a semicolon, appending the entire string with INSERT INTO tbl_name (id, description) VALUES would result in a SQL query that you could execute.

  6. Using joins and aliases something like this:


    SELECT Message_Text
       , Author.Username
       , Receiver.Username
    FROM Table_2 AS 
       INNER JOIN Table_3 ON Table_2.Message_ID = Table_3.Message_ID
       INNER JOIN Table_1 AS Author ON Table_3.Author_ID = Author.User_ID
       INNER JOIN Table_1 AS Receiver ON Table_3.Receiver_ID = Receiver.User_ID

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.