Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

SQL : Filter the newly added elements on existing ones

I have a Model containing Propertyes.

So, I have a table Models, a Table Properties, and a table ModelProperties (ModelId, PropertyId, Value)

Actually when some new Property is/are added (in table Properties), I would like to update the ModelProperties table as well to add the newly added Properties to each of existing Models.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

See the SQL Fiddle here

Should be careful cause some of the newly created Models could already have the new properties, so in order to update properly I need for each of the existing Model to

  1. Select all new Properties to add to that Model (PropertyId exist in in Properties but not in ModelProperties for that ModelId)

  2. Add to the Model the new Properties it does not have (with empty values).

Example:
I have Model 1 and Model 2 with properties till 100. Two new properties were added: 101 and 102. Model 2 was updated to the property 101, but not yet 102. My selection should give/update. My sql should give me the models/properties to update:

ModelIdToUpdate PropertyIdToAdd

1               101
1               102

2               102

Q: What should be the sql script to give the result above?

SQL

CREATE TABLE "Models" (
  "Id" int NOT NULL,
  PRIMARY KEY ("Id")
);
CREATE TABLE "Properties" (
  "Id" int NOT NULL,
  PRIMARY KEY ("Id")
);
CREATE TABLE "ModelProperties" (
  "ModelId" int NOT NULL,
  "PropertyId" int NOT NULL,
  "Value" int NULL,
  PRIMARY KEY ("ModelId", "PropertyId")
);

INSERT INTO "Models"     ("Id") VALUES (1), (2);    
INSERT INTO "Properties" ("Id") VALUES (99), (100); -- existing    
INSERT INTO "Properties" ("Id") VALUES (101), (102); -- new
  
INSERT INTO "ModelProperties" ("ModelId", "PropertyId") VALUES
  (1, 99), (1, 100),
  (2, 99), (2, 100), (2, 101); -- Model 2 updated to 101```

the select

select * from Properties p
left join ModelProperties mp on 
mp.PropertyId = p.Id
where mp.PropertyId is NULL

>Solution :

If you CROSS JOIN your models and your properties, you can then use NOT EXISTS to remove those that have a record in your ModelProperties table, then use this as the basis for inserting your empty properties:

INSERT INTO ModelProperties (ModelId, PropertyId)
SELECT  m.Id,
        p.Id
FROM    Properties p
        CROSS JOIN Models AS m
WHERE   NOT EXISTS 
        (   SELECT  1 
            FROM    ModelProperties AS mp 
            WHERE   mp.ModelId = m.Id 
            AND     mp.PropertyId = p.Id
        );

Example on SQL Fiddle

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading