Database SQL Tips

Creating a Crosstab or Pivot table using transferrable generic SQL

Though some DBMS do have specific crosstab or pivot table features the following SQL favours a transparant generic code easily transferrable between most standard database systems.

Applies to SQL Server, MySQL, Absolute Database, SQLMemTable and others.
See code examples.

Creating a Crosstabs with Paradox using stored SQL's equivalating VIEWs

Though Paradox basically does not support crosstab or pivot table views, creating crosstabs in Paradox actually is possible with a few basic tricks.
Screenprint from Delphi Paradox application:

See code example.

UPDATE table SET column = (SELECT ...

How to update a table from values of another table using a correlated subquery without NULL'ing non-matching records.
UPDATE tablename SET column = (SELECT ...
Applies to Paradox, dBase, Absolute Database, SQLMemTable, SQL Server and others.
See code example.

UPDATE table from other table without subquery

How to update a table from values of another table without using a correlated subquery and without NULL'ing non-matching records.
UPDATE tablenameA SET tablenameA.column = tablenameB.column FROM ...
Applies to SQL Server, MySQL, DBISAM and others.
See code example.