Adding a new non-nullable column to a table in MySQL

Question

I have a table for an event, and I have just made a new table for ticketing_information. Each event in the event table should have a ticketing_information, referenced by a unique id. I'm new to sql, so forgive me if this is a very basic/repeated question, but how should I go about making the migration add a new row to the ticketing_information table for every existing event, and giving the event the generated ticketing_information id?

If it helps, some of my sql is attached:

CREATE TABLE `ticketing_information` (
  `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
  `tickets_available` BOOLEAN DEFAULT TRUE NOT NULL,
  `ticketing_url` VARCHAR(2000) DEFAULT NULL,
  `additional_info_url` VARCHAR(2000) DEFAULT NULL,
  PRIMARY KEY (`id`);)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8; 

ALTER TABLE event
ADD COLUMN ticketing_information_id Int(10) unsigned DEFAULT NULL;

(This is a very stripped-down version of the code, but it should help illustrate the behavior I want)

I was thinking it should be something along the lines of

UPDATE event SET
ticketing_information_id = SELECT `id` FROM `ticketing_information` WHERE <some way of making a new ticketing_information?>;

But I don't really know what I'm doing :)

marc_s · Accepted Answer · 2013-11-12 11:43:45Z

2

how i would perform something like, adding a new non-nullable column to an existing table, would be:

adding that column in first step as nullable, otherwise you will not get the rdbms to modify the table, because it will violate the not null constraint.
updating the new column to the data you want it to have
```
UPDATE event e 
SET e.ticketing_information_id = (SELECT `id` FROM `ticketing_information` WHERE id = e.id)
```
Your update needs to tell the inner select, which row it is currently updating, so you are able to filter only the data of that particular row in the inner select.
modifying the column to be not null

edited Nov 12, 2013 at 11:43

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 12, 2013 at 11:40

Dom84

8669 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user359135 Over a year ago

Couldn't you just do UPDATE event SET ticketing_information_id = id? not that it matters..

Dom84 Over a year ago

Those aren't the same tables. One is 'event' and the other 'ticketing_information'. If i understood correct by the information of the OP. It was just a guess that the inner select could be linked by id with the update statement. It should only show how to do something like this, how this will be implemented in the end i can't tell, i don't know the relations.

user359135 Over a year ago

Sorry I did not read your query properly.. to be honest I just got a little worried that there was some reason i didn't know about for using this technique when it relates to one table only ;) thanks

Dom84 Over a year ago

if you only want to update one field with the content of another field in the same table you can do it like you suggested.

fancyPants Over a year ago

1. there was no word lost about making the column not null, or did I miss it? 2. Even if it was, one would solve this using default values. 3. A nicer to read and maybe faster query (if your query doesn't internally get optimized to following syntax anyway): update event e inner join ticketing_information t on e.whatever = t.whatever set e.whatever = t.whatever

|

Collectives™ on Stack Overflow

Adding a new non-nullable column to a table in MySQL

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related