Found at: http://publish.ez.no/article/articleprint/13 |
Selects in Inserts |
One thing you often get with a PHP Project is a big SQL file with a lot of inserts and create statements. Nothing wrong with that. But often people use the ID from an earlier insert directly in another statement. Nothing wrong with that except that readability sinks to zero, especially for someone trying to figure out what is going on.
Therefore, I'll divulge a simple use of selects in queries. This work with all but self join queries (typically creating a hierarchy where both the ID and the ParentID refers to the same table).
As a little aside it is possible to use selects in DEFAULT statements as well, so I'll throw that into my example as well.
CREATE TABLE DrinkType
(
ID int(11) DEFAULT '0' NOT NULL AUTO_INCREMENT,
Name varchar(255) NOT NULL,
PRIMARY KEY (ID),
UNIQUE KEY (Name)
);
INSERT INTO DrinkType (Name) VALUES ('Ale');
INSERT INTO DrinkType (Name) VALUES ('Wine');
INSERT INTO DrinkType (Name) VALUES ('Soda');
INSERT INTO DrinkType (Name) VALUES ('Spirits');
INSERT INTO DrinkType (Name) VALUES ('Unknown');
CREATE TABLE Drinks
(
ID int(11) DEFAULT '0' NOT NULL AUTO_INCREMENT,
DrinkTypeID int(11) DEFAULT
'SELECT ID FROM DrinkType WHERE Name = \'Unknown\''
NOT NULL REFERENCES DrinkType(ID),
Name varchar(255) NOT NULL,
PRIMARY KEY (ID),
KEY (DrinkTypeID),
KEY (Name)
);
INSERT INTO Drinks (DrinkTypeID, Name) SELECT ID,
'Coors' FROM DrinkType WHERE Name = 'Ale';
INSERT INTO Drinks (DrinkTypeID, Name) SELECT ID,
'Johnnie Walker' FROM DrinkType WHERE Name = 'Spirits';
INSERT INTO Drinks (Name) VALUES 'Johnnie Coor';
|