All In One Script
MENU

How to 'insert if not exists' in MySQL?

by 4:10:00 AM
undefined
How to 'insert if not exists' in MySQL? I started by googling, and found this article which talks about mutex tables. I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I want to insert does not already exist without using a pair of queries (ie, one query to check and one to insert is the result set is empty)? Does a unique constraint on a field guarantee the insert will fail

How to import an SQL file using the command line in MySQL?

by 3:08:00 AM
undefined
How to import an SQL file using the command line in MySQL? I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and I tried this command database_name < file.sql It is not working I get syntax errors. How can I import this file without a problem? Do I need to create a database first?

How to reset AUTO_INCREMENT in MySQL?

by 3:06:00 AM
undefined
How to reset AUTO_INCREMENT in MySQL? How can I reset the auto-increment of a field? I want it to start counting from 1 again. Answer: You can reset the counter with: ALTER TABLE tablename AUTO_INCREMENT = 1 For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel):        Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is

Can I concatenate multiple MySQL rows into one field?

by 3:03:00 AM
undefined
Can I concatenate multiple MySQL rows into one field? Using MySQL, I can do something like: SELECT hobbies FROM peoples_hobbies WHERE person_id = 5; and get: shopping fishing coding but instead I just want 1 row, 1 col: shopping, fishing, coding The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like. I've looked for a function on MySQL Doc and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets, so does anyone

What is the difference between UNION and UNION ALL?

by 11:45:00 PM
undefined
What is the difference between UNION and UNION ALL? What is the difference between UNION and UNION ALL. Answer: UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not. There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports). UNION Example: SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

How to list the tables in an SQLite database file that was opened with ATTACH?

by 11:40:00 PM
undefined
How to list the tables in an SQLite database file that was opened with ATTACH? What SQL can be used to list the tables, and the rows within those tables in a SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool? Answer: The .tables, and .schema "helper" functions don't look into ATTACHed databases: they just query the SQLITE_MASTER table for the "main" database. Consequently, if you used ATTACH some_file.db AS my_db; then you need to do SELECT name FROM

Insert into … values ( SELECT … FROM … )

by 11:38:00 PM
undefined
Insert into … values ( SELECT … FROM … ) I am trying to insert into a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (MySQL, Oracle, SQL Server, Informix, and DB2). Is there a silver-bullet syntax coming from an SQL standard (for example, SQL-92) that would allow me to insert the values without worrying about the underlying database? Answer: Try: INSERT INTO

Instagram