MySQL OPTIMIZE all tables?
You can use mysqlcheck to do this at the command line. One database: All databases:
sql
Foreign key references invalid table
Please see in this SQLfiddle link, Link
Solutions for INSERT OR UPDATE on SQL Server
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?
LEFT function in Oracle
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
SQL – IF EXISTS UPDATE ELSE INSERT INTO
What I’m trying to do is INSERT subscribers in my database, but IF EXISTS it should UPDATE the row, ELSE INSERT INTO a new row. Ofcourse I connect to the database first and GET the $name, $email and $birthday from the url string. This works, but just adds the new row; Here’s what I tried; … Read more
how to increment integer Columns value by 1 in SQL
My questions is how to increment a column’s value by 1. For example, suppose a column ID has values 1,2,3,4, .. Now when I update this table then ID column should increment by 1, Now ID will become 2,3,4,5, ..
ERROR 1115 (42000): Unknown character set: ‘utf8mb4’
Your version does not support that character set, I believe it was 5.5.3 that introduced it. You should upgrade your mysql to the version you used to export this file. The error is then quite clear: you set a certain character set in your code, but your mysql version does not support it, and therefore … Read more
How to create id with AUTO_INCREMENT on Oracle?
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
ROW_NUMBER() in MySQ
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