в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:16:28Z

1 версия

Новая страница

==Delete records with JOIN==



<source lang="sql">
Drop table DVDs;
Drop table Studios;

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

CREATE TABLE Studios
(
StudID VARCHAR(4) NOT NULL,
StudDescrip VARCHAR(40) NOT 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", 1, 2000, "s105"),
("What", 1, 2001, "s103"),
("Out", 1, 2000, "s101"),
("Falcon", 1, 2000, "s103"),
("Amadeus", 1, 1997, "s103"),
("Show", 2, 2000, "s106"),
("View", 1, 2000, "s107"),
("Mash", 2, 2001, "s106");
select * from DVDs;
DELETE DVDs
FROM DVDs, Studios
WHERE DVDs.StudID=Studios.StudID
AND Studios.StudDescrip="Metro-Goldwyn-Mayer";

select * from DVDs;

</source>

==Delete with JOIN 3==



<source lang="sql">
Drop table DVDs;
Drop table Studios;

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

CREATE TABLE Studios
(
StudID VARCHAR(4) NOT NULL,
StudDescrip VARCHAR(40) NOT 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", 1, 2000, "s105"),
("What", 1, 2001, "s103"),
("Out", 1, 2000, "s101"),
("Falcon", 1, 2000, "s103"),
("Amadeus", 1, 1997, "s103"),
("Show", 2, 2000, "s106"),
("View", 1, 2000, "s107"),
("Mash", 2, 2001, "s106");

select * from DVDs;
DELETE FROM DVDs
USING DVDs, Studios
WHERE DVDs.StudID=Studios.StudID
AND Studios.StudDescrip="Universal Studios";

select * from DVDs;

</source>

==Using the "using join delete" Alternative to Delete Data==



<source lang="sql">
Drop table DVDs;
Drop table Studios;

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

CREATE TABLE Studios
(
StudID VARCHAR(4) NOT NULL,
StudDescrip VARCHAR(40) NOT 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", 1, 2000, "s105"),
("What", 1, 2001, "s103"),
("Out", 1, 2000, "s101"),
("Falcon", 1, 2000, "s103"),
("Amadeus", 1, 1997, "s103"),
("Show", 2, 2000, "s106"),
("View", 1, 2000, "s107"),
("Mash", 2, 2001, "s106");
select * from DVDs;
DELETE FROM DVDs
USING DVDs, Studios
WHERE DVDs.StudID=Studios.StudID
AND Studios.StudDescrip="New Line Cinema";

select * from DVDs;

</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL/MySQL/Join/Delete From Join - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия