Using the 'using join delete' Alternative to Delete Data : Delete From Join : Join SQL / MySQL


SQL / MySQL » Join » Delete From Join »

 

Using the 'using join delete' Alternative to Delete Data



Drop table DVDs;
Drop table Studios;


CREATE TABLE DVDs
(
   DVDID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   DVDName VARCHAR(60NOT NULL,
   NumDisks TINYINT NOT NULL DEFAULT 1,
   YearRlsd YEAR NOT NULL,
   StudID VARCHAR(4NOT NULL
ENGINE=INNODB;


CREATE TABLE Studios
(
   StudID VARCHAR(4NOT NULL,
   StudDescrip VARCHAR(40NOT NULL,
   PRIMARY KEY (StudID)
)
ENGINE=INNODB;

INSERT INTO Studios VALUES ('s101', 'Universal Studios'),
                           ('s102', 'Warner Brothers'),
                           ('s103', 'Time Warner'),
                           ('s104', 'Columbia Pictures'),
                           ('s105', 'Paramount Pictures'),
                           ('s106', 'Twentieth Century Fox'),
                           ('s107', 'Merchant Ivory Production');


INSERT INTO DVDs 
(DVDName, NumDisks, YearRlsd, StudID)
VALUES 
     ('Christmas', 12000's105'),
     ('What',      12001's103'),
     ('Out',       12000's101'),
     ('Falcon',    12000's103'),
     ('Amadeus',   11997's103'),
     ('Show',      22000's106'),
     ('View',      12000's107'),
     ('Mash',      22001's106');

select from DVDs;

DELETE FROM DVDs
USING DVDs, Studios
WHERE DVDs.StudID=Studios.StudID
   AND Studios.StudDescrip='New Line Cinema';


select from DVDs;

           
       



Leave a Comment / Note


 
Verification is used to prevent unwanted posts (spam). .

Follow Navioo On Twitter

SQL / MySQL

 Navioo Join
» Delete From Join