I’m faced with the following and I’m not sure what’s best practice. Consider the following table (which will get large): id PK | giver_id FK | recipient_id FK | date I’m using InnoDB and from what I understand, it creates indices automatically for the two foreign key columns. However, …
Tag: sql
How do I UPDATE from a SELECT in SQL Server?
In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: Is it also possible to update a table with SELECT? I have a temporary table containing the values and would like to update another table using those values. Perhaps something like this: Answer
SQL Conditional / Case Joining / Polymorphic Associations?
I’m trying to implement something similar to Ruby on Rails’ polymorphic relationships. I have the following three tables : Events Users Organisations An event can be owner by either a user or an organisation, so my Events table includes the columns: owner_type and owner_id. I can easily list all e…
Is there a severe performance hit for using Foreign Keys in SQL Server?
I’m trying my best to persuade my boss into letting us use foreign keys in our databases – so far without luck. He claims it costs a significant amount of performance, and says we’ll just have jobs to cleanup the invalid references now and then. Obviously this doesn’t work in practice,…
Database design for monitoring status of applications’ functionalities
I’m creating the database for monitoring status of applications’ functionalities. The logic is following: Each application has its own, specific list of functionalities that I’m monitoring. Each …
sqlite select with condition on date
I have an sqlite table with Date of Birth. I would like to execute a query to select those records where the age is more than 30. I have tried the following but it doesn’t work: Answer Some thing like this could be used: If you are using Sqlite V3.7.12 or greater Dont use date(dateofbirth) just use date…
Oracle: ‘= ANY()’ vs. ‘IN ()’
I just stumbled upon something in ORACLE SQL (not sure if it’s in others), that I am curious about. I am asking here as a wiki, since it’s hard to try to search symbols in google… I just found that when checking a value against a set of values you can do As opposed to the usual So I’m …
MySql conditional order by
I have this table (simplified): CREATE TABLE `my_table` ( `id` INT NOT NULL AUTO_INCREMENT , `item_name` VARCHAR(45) NULL , `price` DECIMAL(10,0) NULL , PRIMARY KEY (`id`) ) I need to select …
Similarity function in Postgres with pg_trgm
I’m trying to use the similarity function in Postgres to do some fuzzy text matching, however whenever I try to use it I get the error: If I add explicit casts to text I get the error: My query is: Do I need to do something to initalize pg_trgm? Answer You have to install pg_trgm. In debian, source this…
Benefits Of Using SQL Ordinal Position Notation?
Background Information Ordinal position notation, AKA ordinals, is column shorthand based on the column order in the list of columns in the SELECT clause, instead of either the column name or column alias. Commonly supported in the ORDER BY clause, some databases (MySQL 3.23+, PostgreSQL 8.0+) support the syn…