How to increment multiple rows with a single SQL statement, each with a different status
Let's say I have this table with two columns
id | column_test
1 | NULL
2 | NULL
3 | NULL
...
as you can see, column_test is now NULL. Now I want to make an update for this table and the final status should look like this:
id | column_test
1 | a
2 | b
3 | c
...
I don't know much about compound SQL statements, he thinks the query being used is a small SQL program that will likely have local variables. Unfortunately I don't know the exact syntax yet (I've been trying to build the query myself in the last 20-30 minutes), maybe I can find some SQL gurus here to help me. -)
[edit: let stick to mysql.]
source to share
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)
source to share
In general, you can set a column to the result function of some other columns and therefore set different rows for 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 will only work for a limited number of rows in your table.
source to share