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.
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
-
Select all new Properties to add to that Model (
PropertyIdexist in inPropertiesbut not inModelPropertiesfor thatModelId) -
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
);