I always default to NOT EXISTS. The execution plans may be the same at the moment but if either column is altered in the future to allow NULLs the NOT IN version will need to do more work (even if no NULLs are actually present in the data) and the semantics of NOT IN if … Read more
Assuming three columns in the table: ID, NAME, ROLE BAD: This will insert or replace all columns with new values for ID=1: INSERT OR REPLACE INTO Employee (id, name, role) VALUES (1, ‘John Foo’, ‘CEO’); BAD: This will insert or replace 2 of the columns… the NAME column will be set to NULL or the … Read more
You can do with the –no-data option with mysqldump command mysqldump -h yourhostnameorIP -u root -p –no-data dbname > schema.sql
update As BrianCampbell points out here, SQLite 3.7.11 and above now supports the simpler syntax of the original post. However, the approach shown is still appropriate if you want maximum compatibility across legacy databases. original answer If I had privileges, I would bump river’s reply: You can insert multiple rows in SQLite, you just need … Read more
If the database you want to restore doesn’t already exist, you need to create it first. On the command-line, if you’re in the same directory that contains the dumped file, use these commands (with appropriate substitutions): C:\> mysql -u root -p mysql> create database mydb; mysql> use mydb; mysql> source db_backup.dump;
String literals Escaping single quotes ‘ by doubling them up → ” is the standard way and works of course: ‘user’s log’ — incorrect syntax (unbalanced quote) ‘user”s log’ Plain single quotes (ASCII / UTF-8 code 39), mind you, not backticks `, which have no special purpose in Postgres (unlike certain other RDBMS) and not … Read more
I see many people use subqueries or else window functions to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it should work in any brand of RDBMS. SELECT t1.* FROM mytable t1 LEFT OUTER JOIN mytable t2 ON (t1.UserId = t2.UserId … Read more
You can use the –ignore-table option. So you could do mysqldump -u USERNAME -pPASSWORD DATABASE –ignore-table=DATABASE.table1 > database.sql There is no whitespace after -p (this is not a typo). To ignore multiple tables, use this option multiple times, this is documented to work since at least version 5.0. If you want an alternative way to … Read more
char(13) is CR. For DOS-/Windows-style CRLF linebreaks, you want char(13)+char(10), like: ‘This is line 1.’ + CHAR(13)+CHAR(10) + ‘This is line 2.’
SELECT t1.name FROM table1 t1 LEFT JOIN table2 t2 ON t2.name = t1.name WHERE t2.name IS NULL Q: What is happening here? A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such … Read more