How do we add an equality predicate to an SQL MERGE?

[whoward]

So I know that virtual rows suggest you use the hash syntax with #where(...) instead of testing for equality in the virtual row, for example:

# this won't work:
DB[:some_table].where { some_column == 'xyz' }
# but this will:
DB[:some_table].where(some_column: 'xyz')

however I see that virtual rows are used to build the predicate when building up an SQL MERGE query. Since it doesn't seem possible to add equality predicates in the virtual row, how would one build a query like this?

MERGE INTO m1 USING m2 ON (i1 = i2)
WHEN NOT MATCHED AND some_column = 'xyz' THEN INSERT (i1, a) VALUES (i2, (b + 11))

In my use case I'm merging a list of CDC changes captured in a table that has a column that defines what kind of change occurred on a row (i.e. "insert", "update" or "delete") so depending on the logic I sometimes need to qualify rules based on this column.

[janko]

You can use the =~ operator for equality, i.e. some_column =~ "xyz" (it's mentioned in this guide).

[whoward]

oh perfect - thanks so much!

for some reason I discounted the =~ operator, probably because it's usually used as a 'similarity' operator in Ruby. But I'm glad to see there is an option!