如何更新3个表中的值，而不管该值是否存在？有或没有连接

Question

问题是：

当我在redo表中更新redo时，我希望这些更改影响到daily和lab，而不管Tom是存在于daily还是lab中。即使他不存在于daily或lab中，我也希望只在redo表中进行更新。

见下表：

第一表redo

​

​

第二表daily

​

​

第三表lab

​

​

我试过的是：

UPDATE redo,daily,lab SET
      redo.name = '$newName', daily.name = '$newName', lab.name = '$newName',
      redo.place = '$newPlace', daily.place = '$newPlace', lab.place = '$newPlace',
      redo.code = '$newCode', daily.code = '$newCode', lab.code = '$newCode',
      redo.age = '$newAge', daily.age= '$newAge', lab.age = '$newAge',
      redo.date = redo.date, daily.date = daily.date, lab.date = lab.date,
      redo.contact = '$newContact',daily.contact = '$newContact', lab.contact='$newContact',
      redo.secondarycontact = '$newSecondaryContact',
      daily.secondarycontact = '$newSecondaryContact',
      lab.secondarycontact = '$newSecondaryContact'
       WHERE redo.no='$no' AND 
(redo.name=daily.name AND redo.name=lab.name) AND 
(redo.place=daily.place AND redo.place=lab.place)

结果：

只有在所有3个表同时存在时，才会更新它们。

Answer 1

考虑到您的用例和关系，如果它是我的项目，我将这样设置表：

注意：

• 我在下面对您的字段类型做了一些假设，这可能是不正确的，但是根据您的用例需要对它们进行调整应该是非常简单的。
• 使用这种结构将需要稍微复杂一些或经过深思熟虑的查询，但是结果将更易于维护，并且更容易出错。
• 我会用正规化的名称列，但我选择使用类似于您似乎正在使用的命名约定的名称来保持简单，因为这个问题不是关于“最佳实践命名列”等等。

创建表

创建一个“活动”表，将所有类型的活动保存在一个地方，注意"type“列，列的值可能为'lab‘、'redo’、'daily‘

CREATE TABLE `activities` (
  `no` bigint(20) NOT NULL AUTO_INCREMENT,
  `consultation` varchar(255) DEFAULT NULL,
  `lab` varchar(255) DEFAULT NULL,
  `token` VARCHAR(45) NULL,
  `detailsid` bigint(20) NOT NULL,
  `foreignkey` bigint(20) DEFAULT NULL,
  `type` enum('lab','redo','daily') NOT NULL,
  `date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`no`)
);

创建一个“活动用户”表，以保存与我们的“活动”相关的人的详细信息。

CREATE TABLE `activitiyusers` (
  `no` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `gender` ENUM('M', 'F') NOT NULL,
  `age` SMALLINT NULL,
  `contact` VARCHAR(255) NOT NULL,
  `secondarycontact` VARCHAR(255) NULL,
  `place` VARCHAR(255) NULL,
  `code` VARCHAR(45) NULL,
  `createdat` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  `updatedat` VARCHAR(45) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`no`));

插入一些测试数据

插入3个新的“活动用户”

INSERT INTO `activitiyusers` (`name`, `gender`, `age`, `contact`, `secondarycontact`, `place`, `code`) VALUES ('Tom', 'M', '31', '1212121', '3434343', 'California', '1');
INSERT INTO `activitiyusers` (`name`, `gender`, `age`, `contact`, `secondarycontact`, `place`, `code`) VALUES ('Jack', 'M', '45', '99999', '11111', 'Colorado', '2');
INSERT INTO `activitiyusers` (`name`, `gender`, `age`, `contact`, `secondarycontact`, `place`, `code`) VALUES ('Harry', 'M', '99', '112233', '998877', 'Texas', '3');

插入3项新的“重做”活动，其中一项与我们的3项“活动用户”有关

INSERT INTO `activities` (`token`, `detailsid`, `foreignkey`, `type`) VALUES ('0', '1', NULL, 'redo');
INSERT INTO `activities` (`token`, `detailsid`, `foreignkey`, `type`) VALUES ('0', '2', NULL, 'redo');
INSERT INTO `activities` (`token`, `detailsid`, `foreignkey`, `type`) VALUES ('0', '3', NULL, 'redo');

插入两个新的“每日”活动，一个与Tom和'redo‘活动1有关，另一个与Harry和'redo’活动3有关。

INSERT INTO `activities` (`consultation`, `detailsid`, `foreignkey`, `type`) VALUES ('Cough and Cold', '1', '1', 'daily');
INSERT INTO `activities` (`consultation`, `detailsid`, `foreignkey`, `type`) VALUES ('Panadol', '3', '3', 'daily');

插入2个新的“实验室”活动，一个与Jack和'redo‘活动2有关，第二个与Harry和'redo’活动3有关。

INSERT INTO `activities` (`lab`, `detailsid`, `foreignkey`, `type`) VALUES ('Blood Test', '2', '2', 'lab');
INSERT INTO `activities` (`lab`, `detailsid`, `foreignkey`, `type`) VALUES ('Injection', '3', '3', 'lab');

如何处理这些数据的示例：

注意:这些查询将使用加入俱乐部和列和表别名

获取"Tom“的所有”活动“以及Tom的详细信息

SELECT 
    a.no AS activityno,
    a.consultation,
    a.lab,
    a.token,
    a.type,
    a.date,
    au.no AS userno,
    au.name,
    au.gender,
    au.age,
    au.contact,
    au.secondarycontact,
    au.place,
    au.code,
    au.createdat,
    au.updatedate
FROM
    activities a
        JOIN
    activitiyusers au ON a.detailsid = au.no
WHERE
    name = 'Tom';

获取"Jack“的所有”重做类型活动“以及Jack的详细信息

SELECT 
    a.no AS activityno,
    a.consultation,
    a.lab,
    a.token,
    a.type,
    a.date,
    au.no AS userno,
    au.name,
    au.gender,
    au.age,
    au.contact,
    au.secondarycontact,
    au.place,
    au.code,
    au.createdat,
    au.updatedate
FROM
    activities a
        JOIN
    activitiyusers au ON a.detailsid = au.no
WHERE
    name = 'Jack' AND a.type = 'redo';


# Given a known activity that has an id/no of '2', 
# update the details for the activityuser related to that activity 

UPDATE activitiyusers 
SET 
    contact = '22222222',
    age = 46,
    code = 5
WHERE
    no = (SELECT 
            detailsid
        FROM
            activities
        WHERE
            no = 2);

给定一个已知的“回购”活动，其id/no为'3'，获取与该活动相关的所有子活动以及相关活动用户的详细信息。

注意，这利用了一个mysql自连接 (我们正在加入自身上的活动，以获得与给定的redoActivity行相关的subActivity行)。

SELECT 
    redoActivity.no AS redoactivityno,
    subActivity.no AS subactivityno,
    redoActivity.consultation AS redoactivityconsultation,
    subActivity.consultation AS subactivityconsultation,
    subActivity.lab,
    redoActivity.token,
    subActivity.type,
    redoActivity.date AS redoactivitydate,
    subActivity.date AS subactivitydate,
    au.no AS userno,
    au.name,
    au.gender,
    au.age,
    au.contact,
    au.secondarycontact,
    au.place,
    au.code,
    au.createdat,
    au.updatedate
FROM
    activities subActivity
        JOIN activities redoActivity ON subActivity.foreignkey = redoActivity.no
        JOIN activitiyusers au ON redoActivity.detailsid = au.no
WHERE
    redoActivity.no = 3;