What is tableName.* in SQL
is exactly that same as Where it is useful is in a JOIN, for example:
sql
How to count instances of character in SQL Column
In SQL Server:
How to convert java.util.Date to java.sql.Date?
tl;dr How to convert java.util.Date to java.sql.Date? Don’t. Both Date classes are outmoded. Sun, Oracle, and the JCP community gave up on those legacy date-time classes years ago with the unanimous adoption of JSR 310 defining the java.time classes. Use java.time classes instead of legacy java.util.Date & java.sql.Date with JDBC 4.2 or later. Convert to/from java.time if inter-operating with code not yet updated to java.time. Legacy Modern Conversion java.util.Date java.time.Instant java.util.Date.toInstant()java.util.Date.from( … Read more
What’s the best way to dedupe a table?
SELECT DISTINCT <insert all columns but the PK here> FROM foo. Create a temp table using that query (the syntax varies by RDBMS but there’s typically a SELECT … INTO or CREATE TABLE AS pattern available), then blow away the old table and pump the data from the temp table back into it.
Error: Duplicate entry ‘0’ for key ‘PRIMARY’
For those arriving at this question because of the question title (as I did), this solved my problem: This error can indicate that the table’s PRIMARY KEY is not set to AUTO-INCREMENT, (and your insert query did not specify an ID value). To resolve: Check that there is a PRIMARY KEY set on your table, and that the … Read more
Using Excel VBA to run SQL query
Below is code that I currently use to pull data from a MS SQL Server 2008 into VBA. You need to make sure you have the proper ADODB reference [VBA Editor->Tools->References] and make sure you have Microsoft ActiveX Data Objects 2.8 Library checked, which is the second from the bottom row that is checked (I’m using Excel … Read more
Hibernate show real SQL
log4j.properties hibernate.cfg.xml persistence.xml Some frameworks use persistence.xml:
SQL: How to properly check if a record exists
It’s better to use either of the following: The first alternative should give you no result or one result, the second count should be zero or one. How old is the documentation you’re using? Although you’ve read good advice, most query optimizers in recent RDBMS’s optimize SELECT COUNT(*) anyway, so while there is a difference … Read more
List of special characters for SQL LIKE clause
What is the complete list of all special characters for a SQL (I’m interested in SQL Server but other’s would be good too) LIKE clause? E.g. SQL Server: % _ [specifier] E.g. [a-z] [^specifier] ESCAPE clause E.g. %30!%%’ ESCAPE ‘!’ will evaluate 30% as true ‘ characters need to be escaped with ‘ E.g. they’re … Read more