principlesqlMinor

Compare ID and if true update another table

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

updatetrueanotherandcomparetable

Problem

I have 3 tables. 1 Table (Table A) with patients of a certain disease (heart disease for example), and another table with patients (Table B) with all kinds of diseases. The third table is the results table.

What I want to do is compare heart disease patients from table A (table A is only heart disease patients) to kidney disease Patients in table B. IF the there is a hit (returns results) in table B, update the results table with an 'x' under column kidney disease for that ID.

The results table is currently in this format:

ID|Kidney|CHD|Diabetes ... all the way for another 10 types of diseases

To summarise: We check table A against table B with the specified disease name and if there is a return of result for that patientid, the results table updates with an 'x' under that disease name for that specific patientID. I will then rerun the query changing the disease names (from Kidney to cancer for example) to find out if the heart disease patient has cancer?
Sample Data
Table A

PatientID
1
2
3
4
5
.... all the way to 812 (812 patients of heart disease)

Table B

PatientID|Age|Disease1|Disease2|Disease3...all the way to Disease 10
1 50 kidney lung
2 35 kidney heart diabetes
3 94 cancer pneumonia CHD

What is meant to happen is, for every PatientID in Table A, we compare to table B for that specific disease (example kidney) from disease 1-10 in table B, if there is a result, we then update the results table with an 'x' under that specific disease column in the results table. So in our example, for patient 1 and 2 should have an 'x' under kidney.

With our sample data, the result table should look like this when we run for kidney:

ID|Kidney|CHD|Diabetes
1 x
2 x

Can anyone point me in the direction to writing the query for this? The query checks all patientID (812 of them) from table A for a specific disease in table B and if it returns results it updates for that disease and patient with an 'x' in the results t

Solution

I propose implementing a different and more versatile database structure, because the addition of a (base) table that retains persons or patients that are affected by only one particular disease introduces update anomalies and is unnecessary.
Business rules

I consider that the following business rules formulations are of prime importance:

A Person is affected by zero-one-or-many Diseases

A Disease affects one-to-many Persons

Therefore, you want to implement a common many-to-many (M:N) relationship.
Illustrative logical model

Then, from such formulations, I have derived an IDEF1X1 logical model that is shown in Figure 1:

As demonstrated, it is very important to define the entity types that are involved in the business domain under consideration, their attributes and how they are interrelated (including the cardinality of the relevant relationships).

In this case Person and Disease are independent entity types, each with their own attributes, and they are interrelated by way of an associative entity type, that I called PersonDisease, which holds the attributes that pertain to the relationship that takes place between the two mentioned entity types.

Each PersonDisease occurrence (or row once it is stored in a SQL database) is uniquely identified by PersonId along with DiseaseNumber, so I fixed this combination of attributes as the PRIMARY KEY of this associative entity type.

Derivable data

This model indicates that the Table B included in your answer is derivable data (obtained via DML operations that combine or compute columns from multiple tables), and not an entity type, neither independent nor associative; therefore you should not set it up as a base table.
Expository implementation

Consequently, I created the following DDL structure in terms of the logical model discussed above:

-- You should determine which are the most fitting 
-- data types and sizes for all your table columns 
-- depending on your business context characteristics.

-- Also, you should carry out some testing sessions to define 
-- the most convenient INDEXing strategies.

-- As one would expect, you are free to make use of 
-- your preferred (or required) naming conventions.

CREATE TABLE Person
(
    PersonId        INT       NOT NULL,
    FirstName       CHAR(30)  NOT NULL,
    LastName        CHAR(30)  NOT NULL,
    GenderCode      CHAR(3)   NOT NULL,
    BirthDate       DATE      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    CONSTRAINT PK_Person PRIMARY KEY (PersonId),
    CONSTRAINT AK_Person_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
    (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    )
);

CREATE TABLE Disease
(
    DiseaseNumber    INT       NOT NULL,
    Name             CHAR(30)  NOT NULL,
    ParticularColumn CHAR(30)  NOT NULL,
    SpecificColumn   CHAR(30)  NOT NULL,
    Etcetera         CHAR(30)  NOT NULL,
    CreatedDateTime  TIMESTAMP NOT NULL,
    CONSTRAINT PK_Disease      PRIMARY KEY (DiseaseNumber),
    CONSTRAINT AK_Disease_Name UNIQUE      (Name)-- ALTERNATE KEY.
);

CREATE TABLE PersonDisease -- Associative table.
(
    PersonId        INT       NOT NULL,
    DiseaseNumber   INT       NOT NULL,
    DiagnosedDate   DATE      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    CONSTRAINT PK_PersonDisease                 PRIMARY KEY (PersonId, DiseaseNumber), -- Composite PRIMARY KEY.
    CONSTRAINT FK_from_PersonDisease_to_Person  FOREIGN KEY (PersonId)
        REFERENCES Person  (PersonId),
    CONSTRAINT FK_from_PersonDisease_to_Disease FOREIGN KEY (DiseaseNumber)
        REFERENCES Disease (DiseaseNumber)
);

This structure permits

storing n Disease instances (via simple INSERT operations) and

storing the relationship between n Person instances with n Disease instances.

managing Disease data independently of that which pertains to Person.

And avoids

employing ad hoc procedures that have to be carried out due to the update anomalies introduced by an additional disease-specific table.

Sample data

Let us suposse that the relevant database holds the data that follows:

```
-- 'Person' sample data.
INSERT INTO
person (PersonId, FirstName, LastName, GenderCode, BirthDate, CreatedDateTime)
VALUES
(1, 'David', 'Smith', 'M', CURRENT_DATE, '20161031 13:14:02');

INSERT INTO
Person (PersonId, FirstName, LastName, GenderCode, BirthDate, CreatedDateTime)
VALUES
(2, 'Nicole', 'Johnson', 'F', CURRENT_DATE, '20161031 13:14:02');

-- 'Disease' sample data.
INSERT INTO
Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES
(1, 'Disease A', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO
Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES
(2, 'Disease B', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:

Code Snippets

-- You should determine which are the most fitting 
-- data types and sizes for all your table columns 
-- depending on your business context characteristics.

-- Also, you should carry out some testing sessions to define 
-- the most convenient INDEXing strategies.

-- As one would expect, you are free to make use of 
-- your preferred (or required) naming conventions.

CREATE TABLE Person
(
    PersonId        INT       NOT NULL,
    FirstName       CHAR(30)  NOT NULL,
    LastName        CHAR(30)  NOT NULL,
    GenderCode      CHAR(3)   NOT NULL,
    BirthDate       DATE      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    CONSTRAINT PK_Person PRIMARY KEY (PersonId),
    CONSTRAINT AK_Person_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
    (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    )
);

CREATE TABLE Disease
(
    DiseaseNumber    INT       NOT NULL,
    Name             CHAR(30)  NOT NULL,
    ParticularColumn CHAR(30)  NOT NULL,
    SpecificColumn   CHAR(30)  NOT NULL,
    Etcetera         CHAR(30)  NOT NULL,
    CreatedDateTime  TIMESTAMP NOT NULL,
    CONSTRAINT PK_Disease      PRIMARY KEY (DiseaseNumber),
    CONSTRAINT AK_Disease_Name UNIQUE      (Name)-- ALTERNATE KEY.
);

CREATE TABLE PersonDisease -- Associative table.
(
    PersonId        INT       NOT NULL,
    DiseaseNumber   INT       NOT NULL,
    DiagnosedDate   DATE      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    CONSTRAINT PK_PersonDisease                 PRIMARY KEY (PersonId, DiseaseNumber), -- Composite PRIMARY KEY.
    CONSTRAINT FK_from_PersonDisease_to_Person  FOREIGN KEY (PersonId)
        REFERENCES Person  (PersonId),
    CONSTRAINT FK_from_PersonDisease_to_Disease FOREIGN KEY (DiseaseNumber)
        REFERENCES Disease (DiseaseNumber)
);

-- 'Person' sample data.   
INSERT INTO 
    person (PersonId, FirstName, LastName, GenderCode, BirthDate, CreatedDateTime)
VALUES 
    (1, 'David', 'Smith', 'M', CURRENT_DATE, '20161031 13:14:02');

INSERT INTO 
    Person (PersonId, FirstName, LastName, GenderCode, BirthDate, CreatedDateTime)
VALUES 
    (2, 'Nicole', 'Johnson', 'F', CURRENT_DATE, '20161031 13:14:02');


-- 'Disease' sample data.
INSERT INTO 
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES 
   (1, 'Disease A', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO 
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES 
    (2, 'Disease B', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO 
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES 
    (3, 'Disease C', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO 
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES 
    (4, 'Disease D', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

-- 'PersonDisease' sample data.

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
   (1, 1, '20161031', '20161031 13:14:02');

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
    (1, 2, '20161031', '20161031 13:14:02');

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
    (2, 1, '20161031', '20161031 13:14:02');

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
   (2, 2, '20161031', '20161031 13:14:02');

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
    (2, 3, '20161031', '20161031 13:14:02');

INSERT INTO 
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES 
(2, 4, '20161031', '20161031 13:14:02');

SELECT Person.FirstName            AS "Patient first name",
       Person.LastName             AS "Patient last name",
       Disease.Name                AS "Disease name",
       PersonDisease.DiagnosedDate AS "Diagnosed date"
  FROM PersonDisease
  JOIN Disease
    ON Disease.DiseaseNumber = PersonDisease.DiseaseNumber
  JOIN Person
    ON Person.PersonId       = PersonDisease.PersonId;

SELECT PersonDisease.PersonId,
       Person.FirstName,
       Person.LastName,
       PersonDisease.DiseaseNumber,
       Disease.Name
  FROM PersonDisease
  JOIN Person
    ON Person.PersonId              = PersonDisease.PersonId
  JOIN Disease
    ON Disease.DiseaseNumber        = PersonDisease.DiseaseNumber
 WHERE Person.PersonId              IN (SELECT PersonId
                                          FROM PersonDisease
                                         WHERE DiseaseNumber = 3)
   AND PersonDisease.DiseaseNumber <> 3;

Context

StackExchange Database Administrators Q#153898, answer score: 6

Revisions (0)

No revisions yet.