how to upate multiple rows with one single SQL statement, each to a different status

Question

Say I have this two-column table

 id  | column_test

 1   | NULL

 2   | NULL

 3   | NULL

...

as you see, the column_test column now all have value 'NULL'. Now I want to do an update to this table and the final status should look like :

 id  | column_test

 1   | a

 2   | b

 3   | c

...

I know little about compound SQL statements, it looks the query to be used is a small SQL program which is probably going to have local variables involved. Unfortunately I don't know the exact syntax yet(I've tried to build the query myself in the last 20-30 minutes), maybe I can find some SQL gurus here to help me out.-)

[edit:let's stick to mysql.]

Answer 1

In MySQL:

CREATE TABLE mytable (id INT NOT NULL, column_test CHAR(1));

INSERT
INTO    mytable (id)
VALUES
(1),
(2),
(3),
(4);

SET @r := 0;
UPDATE  mytable
SET     column_test = CHAR(96 + (@r := @r + 1))
WHERE   column_test IS NULL
ORDER BY
        id;

SELECT  *
FROM    mytable;

In SQL Server:

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    mytable
        WHERE   column_test IS NULL
        )
UPDATE  q
SET     column_test = CHAR(96 + rn)

Answer 2

In general, you can set the column to a function-result of some other columns and therefore set different rows to different values. One example:

UPDATE mytable
SET column_test = cola+colb/colc

Closer to your question, this should work

UPDATE mytable
SET column_test = Char (ASCII('a') + id-1)

Of course this would only work for a limited number of rows in your table.