Freenode-SQL-FAQ

From TechWiki

level transmitter disability scooters this american life the artistry group sagging breasts wife swappers parties los angeles times poll site domain convention tote bags novelty light just smith two us video will asian cheap dvd movie preteen lolita portals ironforge banker video cavarr zelcnaliouli FAQ for #sql on Freenode.net

Good SQL overview: http://www.1keydata.com/sql/sql.html

If a row exists, update it; otherwise insert a new row (MERGE/upsert)

This is called MERGE by Oracle and DB2, also known as "upsert". REPLACE is a similar construct in MySQL. Also in MySQL INSERT IGNORE can be used to prevent duplicate rows.

• Oracle - http://www.psoug.org/reference/merge.html
• DB2 - http://www.databasejournal.com/features/db2/article.php/10896_3322041_2
• MSSQL/Access - http://www.thescripts.com/forum/thread83939.html
• MySQL - http://www.xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/
• PostgreSQL - REPLACE/MERGE is on the "TODO" list?

Number the rows of a result set (RANK)

Some DBMS have a RANK construct. This numbers each row in a result set.

• MSSQL2005 - http://msdn2.microsoft.com/en-us/library/ms176102.aspx
• Other - http://www.1keydata.com/sql/sql-rank.html

Add the rows of one query to the bottom of another (UNION)

Use a UNION construct to join rows vertically in a result set. The number of columns in each SELECT statement must match (and must be of the same type).

e.g. SELECT field1, field2 FROM table1 UNION SELECT field1, field3 FROM table2

      table1                 table2
| field1 | field2 |    | field1 | field3 |
-------------------    -------------------
|   0    |    1   |    |   2    |    3   |

                  result
            | field1 | field2 |
            -------------------
            |   0    |    1   |
            |   2    |    3   |