Thursday, March 20, 2008

Sometimes It's The Little Things That Count (3)

The Panorama beta now lets you code SET column = DEFAULT in an UPDATE statement.

"So what?" you say? Well, let's say you write some code that resets a column to have the default value as defined for that column in the original CREATE TABLE.

Now let's suppose the DEFAULT is changed via ALTER TABLE... if you had used the SET column = DEFAULT in your UPDATE you don't have to fix anything.

Can I have another "Woohoo!" please?
CREATE TABLE t (
pkey INTEGER NOT NULL PRIMARY KEY,
data INTEGER NOT NULL DEFAULT '1' );

INSERT t VALUES ( 1, DEFAULT );
INSERT t VALUES ( 2, DEFAULT );

UPDATE t SET data = 98 WHERE pkey = 1;
UPDATE t SET data = 99 WHERE pkey = 2;

UPDATE t SET data = DEFAULT WHERE pkey = 1;

ALTER TABLE t ALTER data DEFAULT 2;

UPDATE t SET data = DEFAULT WHERE pkey = 2;

SELECT * FROM t ORDER BY pkey;

pkey,data
1,1
2,2

2 comments:

ch.lambeau said...

Breck

We all know that NULL is not a value (and we must write WHERE ... IS NULL and not WHERE ... = NULL)

I personally regret SET ... = NULL (instead of SET ... TO NULL)

In the same way (and because no DEFAULT specification for field definition is equivalent to DEFAULT NULL) this new feature would be nice SET ... TO DEFAULT

Hair cutting ? Othogonalisation is never a bad thing !
(I know rdbms makers must abide by requirements like ISO ones)

Chris

Breck Carter said...

Chris...

NULL is not a value? hmmmm...

I thought we should not write WHERE A = NULL because of the bizarre and disgusting *properties* of NULL, not because of its status as value or not a value.

Anyway, I do need a haircut :)

Breck Often Not Pertinent