Re: UPDATE OR REPLACE?

> >>> In my particular situation the case I care about is when the
> result
> >> of an UPDATE is two identical rows. All I really want is a DISTINCT
> >> option.
> >>
> >> Assuming I am following correctly what you want is that the result
> of
> >> an UPDATE not be two identical rows.
> >
> > Correct. In practice I don't care whether the action is IGNORE or
> REPLACE (in Sqlite terms), the outcome is the same.
>
> It is not:
> https://www.sqlite.org/lang_conflict.html
>
> >
> > Obviously two different records that share the same primary key is a
> bad thing and worth an error. Two identical records is just boring.
>
> I do not see how the Sqlite mechanism achieves that. It only looks at
> UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints. It is not
> looking at the record in its entirety.

True: a 'distinct' option is lacking. So for this purpose I use a uniqueness constraint on the whole row.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org