我不知道这是否可能,但我有两个表,userBasic和carPlateConfidence,在carPlateCoffidence中,我想在匹配电子邮件的地方插入userBasic的id。
$query .= "INSERT IGNORE INTO userBasic (id_uM, userNameG, userEmailG) values ((SELECT id_uM FROM userMore WHERE userEmailG='$userEmailG'),'$userNameG', '$userEmailG');";
$query .= "INSERT IGNORE INTO carPlateConfidence (emailConfid, id_uB,plateNumber, confidencePlate, plateNumberUn) values ('$userEmailG', (SELECT id_uB FROM userBasic WHERE userEmailG='(SELECT max(emailConfid) FROM carPlateConfidence)'), '$plateNumber','$confidencePlate', '$plateNumberUn');";
所以如果我有:
userBasic:
id_uM = 555;
userNameG = BlaBla;
userEmailG = blabla@blabla.com
在这张桌子上,我想要
carPlateConfidence:
emailConfid = blabla@blabla.com;
id_uB = 555
plateNumber = 1111
confidencePlate = 70
plateNumberUn = 2222
如果电子邮件不匹配:
emailConfid = blabla2@blabla.com;
id_uB = NULL
plateNumber = 1111
confidencePlate = 70
plateNumberUn = 222
p>S>目前我已经尝试过,从userBasic:中选择id
(SELECT id_uB FROM userBasic WHERE userEmailG='(SELECT max(emailConfid) FROM carPlateConfidence)')
carPlateConfidence中的id_uB设置为外键;
表格:
--
-- Table structure for table `carPlateConfidence`
--
DROP TABLE IF EXISTS `carPlateConfidence`;
CREATE TABLE IF NOT EXISTS `carPlateConfidence` (
`id_cof` int(11) NOT NULL AUTO_INCREMENT,
`id_uB` int(11) NOT NULL,
`emailConfid` varchar(50) NOT NULL,
`plateNumber` varchar(10) NOT NULL,
`confidencePlate` varchar(10) DEFAULT NULL,
`plateNumberUn` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id_cof`),
KEY `id_uB` (`id_uB`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
-- --------------------------------------------------------
--
-- Table structure for table `userBasic`
--
DROP TABLE IF EXISTS `userBasic`;
CREATE TABLE IF NOT EXISTS `userBasic` (
`id_uB` int(11) NOT NULL AUTO_INCREMENT,
`id_uM` int(11) NOT NULL,
`userNameG` varchar(50) NOT NULL,
`userEmailG` varchar(50) NOT NULL,
PRIMARY KEY (`id_uB`),
UNIQUE KEY `userEmailG` (`userEmailG`),
KEY `id_uM` (`id_uM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=119 ;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `carPlateConfidence`
--
ALTER TABLE `carPlateConfidence`
ADD CONSTRAINT `carPlateConfidence_ibfk_1` FOREIGN KEY (`id_uB`) REFERENCES `userBasic` (`id_uB`);
--
-- Constraints for table `userBasic`
--
ALTER TABLE `userBasic`
ADD CONSTRAINT `userBasic_ibfk_1` FOREIGN KEY (`id_uM`) REFERENCES `userMore` (`id_uM`);
所以你想要更新,而不是插入:
UPDATE carPlateConfidence t
SET t.id_uB = (SELECT distinct s.id_uM FROM userBasic s
WHERE s.userEmailG = t.emailConfid)
只有当只能有一个匹配时,这才会起作用,如果可以有多个匹配,你应该指定你想要的一个,如果没有关系,使用MAX()
或limit
:
UPDATE carPlateConfidence t
SET t.id_uB = (SELECT max(s.id_uM) FROM userBasic s
WHERE s.userEmailG = t.emailConfid)