CREATE TABLE Movie ( Movie_ID INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, Produ
ID: 3602753 • Letter: C
Question
CREATE TABLE Movie(
Movie_ID INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
Product_ID INT(11) NOT NULL,
Movie_Code VARCHAR(255),
M_SD VARCHAR(255),
M_Name VARCHAR(255),
Address_ID INT(11) NOT NULL,
M_No VARCHAR(255),
FOREIGN KEY (Address_ID) REFERENCES Address(Address_ID),
FOREIGN KEY (Product_ID) REFERENCES Product(Product_ID)
);
ALTER TABLE Movie AUTO_INCREMENT=401;
INSERT INTO Movie VALUES(NULL,303,"fp12","2016-9-11 13:10","Kingsmen",8,"2A");
INSERT INTO Movie VALUES(NULL,305,"1239","2016-08-05 15:30","Of Mice and Men",9,"12F");
INSERT INTO Movie VALUES(NULL,306,"782g","2016-12-10 10:00","Blade Runner",10,"32D");
CREATE TABLE Address(
Address_ID INT(11) Primary KEY NOT NULL AUTO_INCREMENT,
Street VARCHAR(255) ,
City VARCHAR(255),
State VARCHAR(255),
Zip VARCHAR(255),
Country VARCHAR(255) NOT NULL
);
INSERT INTO Address VALUES (NULL,"3894 Gerald L. Bates Drive","Cambridge","MA","02141","USA");
INSERT INTO Address VALUES (NULL,"Bolman Court","Edmen","IL","62635","USA");
INSERT INTO Address VALUES (NULL,"2875 Vernon St","Los Angeles","CA","90017","USA");
INSERT INTO Address VALUES (NULL,"2850 Ash Avenue","Kirkwood","MO","63122","USA");
INSERT INTO Address VALUES (NULL,"2787 Melody Lane","Richmond","VA","23224","USA");
INSERT INTO Address VALUES (NULL,"1921 Passaic St","Washington","DC","20009","USA");
INSERT INTO Address VALUES (NULL,"110 Dawson Drive","Jacksonville","AR","72099","USA");
INSERT INTO Address VALUES (NULL,"1101 P Street","Lincoln","NE","68506","USA");
INSERT INTO Address VALUES (NULL,"220 North 66th Street","Lincoln","NE","68506","USA");
INSERT INTO Address VALUES (NULL,"2920 Pine Lake Road","Lincoln","NE","68516","USA");
INSERT INTO Address VALUES (NULL,"317 S 17th St","Lincoln","NE","68588","USA");
INSERT INTO Address VALUES (NULL,"1311 O st","Lincoln","NE","68503","USA");
INSERT INTO Address VALUES (NULL,"727 S 11th St","Lincoln","NE","68506","USA");
INSERT INTO Address VALUES (NULL,"210 N 7th St","Lincoln","NE","68506","USA");
INSERT INTO Address VALUES (NULL,"301 N 8th St","Lincoln","NE","68588","USA");
INSERT INTO Address VALUES (NULL,"808 R St #200","Omaha","NE","68500","USA");
How do I write a query to cahnge the address of a theater in a movie ticket record? (mySQL)
Explanation / Answer
Here i have provide query to update address of theatre based on on movie name. in below please update movie name for m-name field and in the same way address fields instead of SOME DATA text.
I see that you have metioned Product_id as foreign key and you have not posted Product table details. if you want to update based on Product ID details use Product_ID in place of address_id and use Products table instead of address table.
UPDATE ADDRESS SET STREET='SOME DATA',CITY='SOME DATA',STATE='SOME DATA',ZIP='SOME DATA',COUNTRY='SOME COUNTRY' WHERE ADDRESS_ID = SELECT ADDRESS_ID FROM MOVIE WHERE M_NAME'SOME MOVIE NAME')