Hi everyone,
If, using MySQL, I have to select 'playerID' from soccer.players based on
lname='smith', fname='john', and dob='1988-12-02' and THEN update
soccer.teams 'coachID' equal to the result of the select statement, can I do
it all in one query? Or must I let my PHP script run a select, return the
result, and then use that in the update statement?
The scenario is this: I've just created the player record in soccer.players
(it's an adult league, so the players can also be the coach) and need to
grab that playerID to insert into the coachID section of soccer.teams.
Here's the SQL used to create it all if you're wondering about my table structures:
CREATE DATABASE soccer;
USE soccer;
CREATE TABLE `divisions` (
`divisionID` int(11) NOT NULL auto_increment,
`division_name` varchar(15) NOT NULL default '',
UNIQUE KEY `divisionID` (`divisionID`)
) TYPE=MyISAM COMMENT='league division names with identifier'
AUTO_INCREMENT=1 ;
CREATE TABLE `teams` (
`teamID` int(11) NOT NULL auto_increment,
`team_name` varchar(20) NOT NULL default '',
`coachID` int(11) NOT NULL default '0',
`divisionID` int(11) NOT NULL default '0',
`paid` enum('y','n') NOT NULL default 'n',
`pmnt_notes` text,
`win` int(2) NOT NULL default '0',
`loss` int(2) NOT NULL default '0',
`tie` int(2) NOT NULL default '0',
`goals_for` int(2) NOT NULL default '0',
`goals_against` int(2) NOT NULL default '0',
PRIMARY KEY (`teamID`)
) TYPE=MyISAM COMMENT='Team registration and statistical info'
AUTO_INCREMENT=1 ;
CREATE TABLE `players` (
`playerID` int(11) NOT NULL auto_increment,
`lname` varchar(20) NOT NULL default '',
`fname` varchar(20) NOT NULL default '',
`teamID` int(11) NOT NULL default '0',
`dob` date NOT NULL default '0000-00-00',
`address` varchar(40) NOT NULL default '',
`telephone` int(9) NOT NULL default '2147483647',
`email` varchar(40) NOT NULL default '',
`sex` enum('m','f') NOT NULL default 'm',
`yellow` int(2) NOT NULL default '0',
`red` int(2) NOT NULL default '0',
`disc_notes` text,
`goals_scored` int(2) NOT NULL default '0',
`own_goals` int(2) NOT NULL default '0',
PRIMARY KEY (`playerID`)
) TYPE=MyISAM COMMENT='player registration and statistical info'
AUTO_INCREMENT=1 ;
Thanks,
Kirk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
