Adding Elements that are new

[LevendParticipant]

Sorry for the misleading title, let me explain it in English and SQL:

In SQL I have a table (“Master”) and a load table (“Load”).
Now I want to add all elements/records in “Load” that do not exist in “Master”.
With SQL I would do something like

SELECT * FROM Load LEFT OUTER JOIN Master ON Load.SomeField = Master.SomeField WHERE Master.AField IS NULL

The resulting records would then be inserted in Master.

How can I do the above SQL statement using OCL?

Thanks
Levend

[Peter BuchmannParticipant]

First, you should ask your question on stackoverflow with tag mdriven because this forum has actually moved there.

When trying to understand your question, I find some things to be unclear:

1) Maybe I’ve forgotton too much about SQL, but I think that your SQL does other things than you tell. You load Load data rows that have joined rows in Master having a null value in AField. In my understanding you don’t really need the left outer join. A inner join would do the same. Correct?

2) MDriven is not SQL. Doing mass updates is no typical task for ORM tools. The best answer for your question could very well be to simply do it in SQL, just depending on your system architecture.

3) If you want to do it with MDriven, you have to take into account how big your tables are. OCL only makes sense on small tables (maybe like <50000 rows) because it loads the complete table and filters in memory. If you have more rows you have to execute your query “in PS” which means that the query is generated as SQL. You can achieve the same using LINQ.

4) Depending on the change that you want to do, you have to load all objects that have to be changed, change the objects and update the database with these changes.

[LevendParticipant]

Many thanks Peter!

I think I will give Stackoverflow a chance 😉

The SQL statement is absolutely correct. It is the usual way to find out which “Load” records are not in “Master” (bycomparing the “AField”).
(I’m much much more familiar with SQL than with OCL)

Thanks
Levend

[Peter BuchmannParticipant]

But your SQL does more. It would also find lines in Load that have an associated row in Master with a null value in column AField. So your AField mustn’t be nullable so that the SQL is correct by chance. A “Not exists” query would be correct, but maybe a bit slower.

In OCL you could do: Load.AllInstances->select(x|x.Master->IsEmpty) where x.Master is the association from load to Master. This is much clearer than your SQL.

[LevendParticipant]

Hello Peter,
You are absolutely right with the “nullable” issue.
But usually I use the Master table’s Primary Key as the “AField”… so I ensure that it is not nullable by definition.

Thanks
Levend

[Author]

Posts