1

I have table with data on old game characters. I'd like to add a gender column. If I do

   ALTER TABLE characters
   ADD gender ENUM('m','f') AFTER char_name

then I get a column full of NULLs. How do I get the values in? Using an INSERT statement tries to tag them all into new rows, instead of replacing the NULLs. Using an UPDATE statement requires a new statement for every single entry.

Is there any way to just drop a "VALUES ('m'),('f'),('f'),('m'),('f') etc" into the ALTER statement or anything else and update them all efficiently?

DownrightBass
  • 25
  • 6
  • After you add the column you need to update the table to populate the values. – Stu Jan 31 '23 at 22:04
  • With a new UPDATE statement for every single row? – DownrightBass Jan 31 '23 at 22:07
  • 1
    I would suggest two updates, one for `m`, one for `f`, with the appropriate `where` criteria, or a single update using a *case expression* - all depending on your data. – Stu Jan 31 '23 at 22:09
  • 1
    If you have the values available together with the key of your table, you can use the `UPDATE JOIN` statement of mysql. See for instance https://stackoverflow.com/questions/8331687/mysql-update-a-joined-table on how to do this – derpirscher Jan 31 '23 at 22:09
  • 1
    Are the `id`s of the characters all consecutive? If so, a temporary table could be handy. – Jetto Martínez Jan 31 '23 at 22:10
  • Where do you have all the values? – Barmar Jan 31 '23 at 22:14
  • ids are consecutive, yes. I'll look into how temp tables and update/join can apply. The values are just written out. I had an AI pull them and format them as a sequence of ('f'), etc hoping I could copy/paste them somehow – DownrightBass Jan 31 '23 at 22:19
  • *"hoping I could copy/paste them somehow"* maybe if you have some sort of gui that supports this operation. But even if there is one, it will probably support only a limited number of rows for editing. (Like "edit top 200 rows in SSMS) But in the shell, there are only two options: 1) an update query for each row 2) UPDATE JOIN with another table that has the data ... – derpirscher Jan 31 '23 at 22:30
  • Created a temp table, UPDATE JOIN took a second to wrap my head around, but it worked great. Shocked there's no simple command to do this, it must not come up as often as I'd have thought. Thanks, all. – DownrightBass Jan 31 '23 at 22:49

1 Answers1

1

There is no way to fill in specific values during ALTER TABLE. The value will be NULL or else a default value you define for the column.

You may find INSERT...ON DUPLICATE KEY UPDATE is a convenient way to fill in the values.

Example:

CREATE TABLE characters (
  id serial primary key,
  char_name TEXT NOT NULL
);

INSERT INTO characters (char_name) VALUES
 ('Harry'), ('Ron'), ('Hermione');

SELECT * FROM characters;
+----+-----------+
| id | char_name |
+----+-----------+
|  1 | Harry     |
|  2 | Ron       |
|  3 | Hermione  |
+----+-----------+

Now we add the gender column. It will add the new column with NULLs.

ALTER TABLE characters 
  ADD gender ENUM('m','f') AFTER char_name;

SELECT * FROM characters;

+----+-----------+--------+
| id | char_name | gender |
+----+-----------+--------+
|  1 | Harry     | NULL   |
|  2 | Ron       | NULL   |
|  3 | Hermione  | NULL   |
+----+-----------+--------+

Now we update the rows:

INSERT INTO characters (id, char_name, gender) VALUES
  (1, '', 'm'), (2, '', 'm'), (3, '', 'f')
ON DUPLICATE KEY UPDATE gender = VALUES(gender);

It looks strange to use '' for the char_name, but it will be ignored anyway, because we don't set it in the ON DUPLICATE KEY clause. The original char_name is preserved. Specifying the value in the INSERT is necessary only because the column is defined NOT NULL and has no DEFAULT value.

SELECT * FROM characters;

+----+-----------+--------+
| id | char_name | gender |
+----+-----------+--------+
|  1 | Harry     | m      |
|  2 | Ron       | m      |
|  3 | Hermione  | f      |
+----+-----------+--------+

DBFiddle

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    That worked great. Even on a large dataset, if you have the values ready you can run them through a quick function or even have AI put the values into the right format. MySQL flagged that soon it won't work unless you give the values an alias and then replace "VALUES(gender)" with "alias.gender" in the final clause, but that was easy, too. May be a matter of taste between this and the UPDATE JOIN method. Incidentally, this was an uncommonly relevant, clear, and thorough answer. Thank you! – DownrightBass Feb 01 '23 at 17:59