How to update values in 3 tables irrespective of whether the value exists or not? With or without join

Question

The problem:

When I update Tom in the redo table, I want the changes to affect the daily and lab irrespective of whether Tom exists in daily or lab. Even if he doesn't exist in daily or lab I want the update to be made only in the redo table.

Look at the tables below:

First table redo

Second Table daily

Third Table lab

What I tried:

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)

Result:

Values are updated only if they exist in all the 3 tables at the same time.

For what its worth if these 3 tables all have these same columns that should contain the same values and should be updated together to stay in sync, I would consider combining them into one table like `details` to hold all the "name, age, contact, etc" columns that are shared and then in my `redo`, `daily` and `lab` tables I would make a `details_id` column that maps those records to the single `details` record. This way that one single details row is the single source of truth for all 3 of those entities to share and you only have to update one row when changes are made. — Wesley Smith, Oct 01 '20 at 00:30
If the above is an acceptable option for you, let me know and Ill add an answer with implementation details — Wesley Smith, Oct 01 '20 at 00:34
@WesleySmith If I change the value of "code" for patient "Tom" in redo table, I want the value of "code" to be updated in other tables wherever "Tom" exists. — rangerboyy, Oct 01 '20 at 00:38
@WesleySmith I can go with your solution if it solves my problem. Please do post the answer. Your help is much appreciated. — rangerboyy, Oct 01 '20 at 00:41
Will do, quick question, I see that `daily` and `lab` have a `foreignkey` are those related to `redo`? Like a `redo` record can have one or more related `daily` records and/or `lab` records? — Wesley Smith, Oct 01 '20 at 01:14
@WesleySmith The "no" field in "redo" table is stored as the "foreignkey" in "daily" and "lab". yes!!! — rangerboyy, Oct 01 '20 at 01:53
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Oct 01 '20 at 11:09

Wesley Smith · Accepted Answer · 2020-10-01T02:27:24.117

Given your use case and relationships, this is how I would set up the tables if it were my project:

NOTE:

I've made some assumptions about your field types in the below that may not be correct, but it should be trivial to adapt them as needed for your use case.
Using this structure will require slightly more complex or well thought out queries, but the result will be MUCH more maintainable and far less error prone.
I would normaly name columns and such differently, but I've elected to use names similar to the naming convention you seem to be using to keep it simple since this question isnt about "best practices naming columns" etc..

Create the tables

Create an 'activities' table to hold all types of activities in one place, note the "type" column with possible values of '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 an 'activitiyusers' table to hold the details for the people that are related to our "activities"

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`));

Insert some test data

Insert 3 new "activitiyusers"

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');

Insert 3 new "redo" activities, one related to each of our 3 "activitiyusers"

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');

Insert 2 new "daily" activities, one related to Tom and 'redo' activity 1, the second related to Harry and 'redo' activity 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');

Insert 2 new "lab" activities, one related to Jack and 'redo' activity 2, the second related to Harry and 'redo' activity 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');

Examples of how to work with this data:

Note: these queries will make use of join cluases as well as Column and table aliases

Get All "activities" for "Tom", along with Tom's details

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';

Get all "redo type activities" for "Jack", along with Jack's details

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);

Given a known "repo" activity that has an id/no of '3', get all sub activities related to that activity along with details of the related activitiyuser

Note that this utilizes a mysql self join, ie we are joining the activities on itself to get subActivity rows that are related to a given redoActivity row.

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;

How to update values in 3 tables irrespective of whether the value exists or not? With or without join

1 Answers1

NOTE:

Create the tables

Insert some test data

Examples of how to work with this data: