MySQL - COALESCE on an empty string

COALESCE works only on NULLs.  If you have an empty string, COALESCE will return the empty string instead of the second value because the first value is not NULL.

How to return second value or column if first one is NULL or is blank:

SELECT IFNULL(NULLIF(col1,''),col2)

The inner NULLIF returns a NULL if col1 is blank.  The outer IFNULL returns col1 if it's not blank or NULL and col2 otherwise.

ColdFusion - Getting ID of inserted row

How to get ID of row inserted using CFQUERY:

MySQL:

<cfquery result="insertrow" datasource="#application.ds#">
INSERT INTO [table]([column1], [column2]...)
VALUES([value1], [value2]...)
</cfquery>
ID: insertrow.generated_key

T-SQL

<cfquery name="insertrow" datasource="#application.ds#">
INSERT INTO [table]([column1], [column2]...)
VALUES([value1], [value2]...)

SELECT @@IDENTITY AS id
</cfquery>
ID: insertrow.id

Notice that when using MySQL, you use result, while when using T-SQL you use name.

MySQL - ON DUPLICATE KEY

I was adding bridal registry functionality to CartWeaver 3 and was wondering if MySQL, the site is running on ColdFusion with MySQL, has something similar to T-SQL's MERGE. Not only does it have, but the code looks a lot more elegant. One thing to remember though, is that you must set cart/registry user id + sku/product id as UNIQUE.

Here's how this works. You do your regular insert and at the end add a ON DUPLICATE KEY:

INSERT INTO tbl_registryproducts(reguserid, skuid, qty)
VALUES(#session.reguserid#,#form.skuid#,#form.qty#)
       ON DUPLICATE KEY UPDATE qty = qty + #form.qty#

That's it. One line and you're done. And yes, I know I should be using cfqueryparam, but that would make this example a lot longer and a lot less readable.

T-SQL: Set Vs Select

First, let me just say that finally, in MS SQL 2008, you can declare and assign variables at the same time!

DECLARE @var1 [type] = [value1], @var2 [type] = [value2], @var3 [type] = [value3]

What's the difference between SET and SELECT?  SET sounds better and that's pretty much it.  With SET, you can only assign one variable at a time, with SELECT, you can do this:

SELECT @var1 = [value1], @var2 = [value2], @var3 = [value3]

Another great reason to use SELECT for setting variables is this:

SELECT @var1 = [column1], @var2 = [column2], @var3 = [column3
FROM [table]

MySQL - UPDATE with JOIN

The syntax for cross-table update in MySQL is somewhat different than T-SQL. Personally, the way it's done in MySQL makes more sense.
T-SQL:

UPDATE table1
SET col1 = t2.col1, col2 = t2.col2
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id

MySQL:

UPDATE table1 t1 JOIN table2 t2 ON t1.id = t2.id
SET t1.col1 = t2.col2, t1.col2 = t2.col2

SQL - Adding columns to a table

I'm a great proponent of using Enterprise Manager for creating and altering tables. For years I used the GUI exclusively for database management and only used the query analyzer for writing queries and doing quick selects.
In my last projects, I had to create and alter tables using scripts. Since the site was live and I was working on the staging site, the database schema on the live site would have to be altered instantaneously. I looked up how to alter tables using scripts and found something that I could have used on many occasions when altering table in Enterprise Manager.
Enterprise Manager is a great tool, but it has one shortcoming when dealing with tables. If you need to add a column with a default value, you would have to run an update query on the table because most of the time, the column would get added with nulls instead of the default value. If you need to add a column with a not null constraint, that's when it gets fun. You need to the column with a default and not null checked off, save the table, update the table, check not null on and save the table one more time. Annoying, time consuming, not fun. Now lets tackle the same problem using a script:

ALTER TABLE table-name ADD
column-name column-type NOT NULL DEFAULT default-value WITH VALUES

Short and beautiful. DEFAULT specifies a default value and WITH VALUES instructs SQL Server to populate all rows with the default value.

T-SQL - Using CASE in SELECT

Today I had a task of modifying my code on an investment site I made. The task was to change the color of a row if a ticket is near closing time, 30 minutes, or passed it and an email hasn't been sent to the clearing house yet.
I had two choices, either do it inside the query or in ColdFusion. I decided to do it inside the query.

SELECT t.id, trade_date, ...,
CASE WHEN
(
DATEDIFF(n,GETDATE(),CONVERT(CHAR(10),trade_date,110) + ' ' + cutoff_p) <= 30 AND d.amount_p > 0
AND email_date IS NULL
)
OR
(
DATEDIFF(n,GETDATE(),CONVERT(CHAR(10),trade_date,110) + ' ' + cutoff_r) <= 30 AND d.amount_r > 0
AND email_date IS NULL
)
THEN 1 ELSE 0 END AS alert
FROM ticket t JOIN ticket_detail d ON t.id = d.ticketid
LEFT OUTER JOIN fund f ON d.fundcode = f.fundcode
ORDER BY trade_date, account_name

CASE is a very useful statement in SQL. It can be used either as CASE expression WHEN or CASE WHEN expression. Here, I'm using the later form.
I have 2 columns, cutoff_r and cutoff_p. They hold the cutoff times for purchases and redemptions. These colums are not DATETIME, rather they are of type VARCHAR. Since the cutoff values are VARCHAR, they have to be converted to DATETIME.
First, lets add the cutoff times to the date the fund is supposed to be traded:

CONVERT(CHAR(10),trade_date,110) + ' ' + cutoff_p

The trading date is converted into USA standard, or mm-dd-yyyy, a CHAR 10 characters long. Then the cutoff time, which is in the h:mm(A/PM) form, is concatenated at the end. Notice the space in the middle. Without it, the hour will immediately follow year and confuse SQL Server.
The first condition is comparing current time with the new trade date and checking that there's less than 30 minutes until cutoff or that the time has passed, in which case the result will be negative. The second condition checks if the trade is a purchase and the third condition checks if an email was sent. If all are true, then the expression is true.
Next, I add a duplicate expression that checks if the trade is a redemption. I could have put the email sent check outside, but then I would have to enclose whole thing in another pair of parenthesis since this is an OR comparison.
The last part,

THEN 1 ELSE 0 END AS alert

states that if the expression is true, then return 1, else, return 0 and name the column alert.