On Sun, Aug 20, 2017 at 05:17:16PM +0000, Wout Mertens wrote:
> Oh wow, I didn't know about ON CONFLICT, thanks!
>
> Unfortunately the behavior on UPDATE (actually INSERT OR REPLACE in my
> case) is not the desired behavior, it removes the row with the same k but
> different id.
PostgreSQL has some unwieldy but very general "UPSERT" syntax where you
can specify what to do ON CONFLICT in an INSERT on a
per-constraint/colliding columns basis, and with WHERE clauses too.
INSERT INTO .. (id, k, thing1, thing2) VALUES (..)
ON CONFLICT (id) DO UPDATE SET thing1 = .. WHERE id != 5
ON CONFLICT (k) DO UPDATE SET thing2 = .. WHERE k != 3;
That's very generic, but for the common case I'd really like:
-- Insert or else update conflicting rows' columns to all the new
-- values from the insert.
INSERT OR UPDATE INTO .. (..) VALUES (..);
and
INSERT INTO .. (..) VALUES (..)
ON CONFLICT (..) DO UPDATE; -- change all the columns of the
-- conflicting row to the new values
-- from the INSERT
Nico
--
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
