How do I escape a single quote in SQL Server?
Single quotes are escaped by doubling them up, just as you’ve shown us in your example. The following SQL illustrates this functionality. I tested it on SQL Server 2008: Results
sql
How do I perform an IF…THEN in an SQL SELECT?
The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server. You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works: CASE statements can be embedded in other CASE statements and even included in aggregates. SQL Server Denali (SQL … Read more
Drop a temporary table if it exists
From SQL Server 2016 you can just use On previous versions you can use You could also consider truncating the table instead rather than dropping and recreating.
How do I restore a dump file from mysqldump?
It should be as simple as running this: If the dump is of a single database you may have to add a line at the top of the file: If it was a dump of many databases, the use statements are already in there. To run these commands, open up a command prompt (in Windows) … Read more
MySQL Cannot Add Foreign Key Constraint
To find the specific error run this: And look in the LATEST FOREIGN KEY ERROR section. The data type for the child column must match the parent column exactly. For example, since medicalhistory.MedicalHistoryID is an INT, Patient.MedicalHistory also needs to be an INT, not a SMALLINT. Also, you should run the query set foreign_key_checks=0 before running the DDL so you can create the tables in an … Read more
SQL Query with SUM with Group By
The GROUP BY clause forms rows for each unique combinaton of the columns you nominate in that clause. If you want to show a sum for each month, then only include s.month in the group by clause: e.g If you include reseller details in the select clause also include them in the group by clause … Read more
Cannot delete or update a parent row: a foreign key constraint fails
As is, you must delete the row out of the advertisers table before you can delete the row in the jobs table that it references. This: …is actually the opposite to what it should be. As it is, it means that you’d have to have a record in the jobs table before the advertisers. So … Read more
ORA-01843 not a valid month- Comparing Dates
I have a problem when try to select data from a table filtering by date. For example: The Oracle Error is: Probably the source data of table is corrupted, in this case: How can i solve this problem? Can I change this dates for null? The results of this select, select * from nls_session_parameters; , is:
Finding duplicate values in a SQL table
Simply group on both of the columns. Note: the older ANSI standard is to have all non-aggregated columns in the GROUP BY but this has changed with the idea of “functional dependency”: In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, … Read more
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-one: Use a foreign key to the referenced table: You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (student). One-to-many: Use a foreign key on the many side of the relationship linking back … Read more