CODEWITHSUNDEEP
mysqlsql
Stick in the Mud
Stick in the Mud

Reputation: 53

inserting data into a new column of an already exsisting table

I added a new column to an already existing table, I just need to get data into this new column...I assume I can use a variant of the insert command, yet I do not know how to specify the column name and then how to order the values going into the new column.

Upvotes: 2

Views: 13285

Answers (4)

Jorge Campos
Jorge Campos

Reputation: 23361

Ok, after some conversation through the comments lets go to an answer.

I suppose your table is something like id, name, age, dateBirth, etc fields. But whoever create this table forget to add the gender for the registries. As you said that the new column is an sex enum('m', 'f') you will have to update every registry on this table one by one. Like this:

 update matches set sex = 'm' where id = 1;

Pay attention that with this command I just updated the row on the table where the id=1 and Im assuming that id is your primary key. On the where caluse you have to put your primary key, otherwise you may update more then one column.

If your table has many registries there is a way that you can do it cuting down the heavy work (at least a little)

In order to update many rows at once you have to do an Update with a LIKE filter, you will set a filter that can identifie many womans at a time then many men at time as this:

 update matches set sex = 'f' where name like '%Jheniffer%'

Since Jheniffer is a female name most likely you will update every registry which has part of the name as Jheniffer like 'Jheniffer Smith'. So repeat this process for the common names until the work is done. For all womens then repeat for the men.

Hope it help you to understand

Upvotes: 3

LHA
LHA

Reputation: 9645

Why INSERT here? You need to UPDATE data to column inserted. Here is the list of steps

  1. Alter the table then add column with constraint is NULLABLE.

  2. Process the update the column added using UPDATE command.

  3. If you want the column added is NOT nullable. Just re-alter the column and change it to NOT nullable.

Upvotes: 2

Jeribo
Jeribo

Reputation: 465

You can use UPDATE command.

UPDATE my_table SET new_col='new_val'

if you need to set new column value just in few rows add WHERE clause

UPDATE my_table SET new_col='new_val' WHERE condition='true'

Upvotes: 1

Konstantin
Konstantin

Reputation: 3294

you have to use update command, insert is for adding new rows.

update myTABLE set NewColumn = ?

Upvotes: 2

Related Questions