Update records using multiple criteria

#1 · 2025-07-25, 6:02 am

The following code works well if I want to add the word Paternal to all records for a new individual fact (EventType = 1049) that I have created, which I don't.

UPDATE EventTable SET Details = 'Paternal' WHERE EventType = 1049

I would like the word Paternal to only appear in EventType = 1049 if EventType = 1000 contains a specific surname in details (e.g. Details = Watts).

I tried using the following but it does not work

UPDATE EventTable SET Details = 'Paternal' WHERE EventType = 1049
AND(EventType = 1000 AND Details = 'Watts');

Where am I going wrong.

Thanks in advance.

#2 · 2025-07-25, 8:50 am

Your condition is impossible. It is telling UPDATE to work on only each record in the table that simultaneously has two values for EventType but there is only one value for EventType per record. What you want is to update those records having one EventType for those OwnerIDs that also have a record with the other EventType and Details with a specific value. Something along these lines should work:

UPDATE EventTable SET Details = 'Paternal'
WHERE EventType = 1049
AND OwnerID IN
(SELECT DISTINCT OwnerID FROM EventTable WHERE EventType = 1000 AND Details = 'Watts');

Nampara has reacted to this post.

#3 · 2025-07-25, 9:03 am

Thank you so much - that worked perfectly.

#4 · 2025-07-25, 9:36 am

Tom has already explained why the original query didn't work and also how to fix it. I would like to comment on a stylistic issue that you may run into as you develop other queries. Namely, consider the following text:

UPDATE EventTable SET Details = 'Paternal' WHERE EventType = 1049
AND(EventType = 1000 AND Details = 'Watts');

The bit about SET Details = 'Paternal' is fine. However, I have sometimes run into problems with conditions such as AND Details = 'Watts'. Namely, I have run into situations where it didn't work and instead I had to write the condition as AND Details LIKE 'Watts'. In other words, I have sometime had trouble using comparison operators such as = and != on text fields and instead I have had to use LIKE and NOT LIKE. I never quite know when it's going to be a problem and when it's not, so I have adopted the practice always using LIKE and NOT LIKE for comparing text fields. The comparison operators obviously always work on numeric fields.

I wish I could give you a better explanation than that, and obviously the comparison operators did work in your use case. It may have to do with columns where the RMNOCASE collating sequence comes into play. You were using the EventTable.Details column, and the RMNOCASE collating sequence may not be used for that column. Perhaps Tom can provide some additional wisdom on this matter.

The LIKE operator also has the advantage of supporting wildcards if you wish. For example, you can query on Surname LIKE 'smith' or on Surname LIKE 'smith%', whichever one satisfies you use case. The former will pick up only the exact surname Smith and the latter will pick up any surname starting with Smith. I could be wrong, but I don't think wildcards are supported with the comparison operators and they definitely are supported with the LIKE operator.