As far as I can tell, both syntaxes are equivalent. The first is SQL standard, the second is MySQL’s extension. So they should be exactly equivalent performance wise. http://dev.mysql.com/doc/refman/5.6/en/insert.html says: INSERT inserts new rows into an existing table. The INSERT … VALUES and INSERT … SET forms of the statement insert rows based on explicitly … Read more
Distinct and Group By are going to give you different results. To get the results you expect you’ll want to use Seen above, group will return things as a hash. While distinct just returns the number of people in total, seen below.
You can use mysqlcheck to do this at the command line. One database: All databases:
Please see in this SQLfiddle link, Link
Assume a table structure of MyTable(KEY, datafield1, datafield2…). Often I want to either update an existing record, or insert a new record if it doesn’t exist. Essentially: What’s the best performing way to write this?
There is no documented LEFT() function in Oracle. Find the full set here. Probably what you have is a user-defined function. You can check that easily enough by querying the data dictionary: But there is the question of why the stored procedure works and the query doesn’t. One possible solution is that the stored procedure is … Read more
Check to see if your user is mapped to the DB you are trying to log into.
There is no such thing as “auto_increment” or “identity” columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger: Table definition: Trigger definition: UPDATE: IDENTITY column is now available on Oracle 12c: or specify starting and increment values, also preventing any insert into the identity column … Read more
Or just: In method syntax, with other operators: Essentially, you already are selecting all columns, the select then transforms that to the columns you care about, so you can even do things like:
I want the row with the single highest col3 for each (col1, col2) pair. That’s a groupwise maximum, one of the most commonly-asked SQL questions (since it seems like it should be easy, but actually it kind of isn’t). I often plump for a null-self-join: “Get the rows in the table for which no other … Read more