SQL Basic Syntax Study Notes

Multi-table updates

ALTER TABLE student CHANGE COLUMN `province` `province_id` int(11) NOT NULL AFTER `city`;

-- create city table
create table city(id int primary key auto increment, name varchar(64));

-- insert city data from student table into city table

INSERT INTO city(name) SELECT  city from student; select * from city;

-- multi-table update: change the city field in student from a city text string to the corresponding ID in the city table
UPDATE student inner join city on student.city = city.nam
SET student. city city.id

-- update the city field: rename it to city_id and change its type to int 
ALTER TABLE student CHANGE COLUMN city city_id int(11) NOT NULL AFTER province_id;