How to return ids of rows with conflicting values?-CodePudding

I am looking to insert or update values in an SQLite database (version > 3.35) avoiding multiple queries. upsert along with returning seems promising :

CREATE TABLE phonebook2(
  name TEXT PRIMARY KEY,
  phonenumber TEXT,
  validDate DATE
);
INSERT INTO phonebook2(name,phonenumber,validDate)
  VALUES('Alice','704-555-1212','2018-05-08')
  ON CONFLICT(name) DO UPDATE SET
    phonenumber=excluded.phonenumber,
    validDate=excluded.validDate
  WHERE excluded.validDate>phonebook2.validDate RETURNING name;

This helps me track names corresponding to inserted/modified rows. How to find rows where phonebook2 values conflict with values upserted in above statement, but no insert or update happened due to where clause?

CodePudding user response：

The RETURNING clause can't be used to get non-affected rows.

What you can do is execute a SELECT statement before the UPSERT:

WITH cte(name, phonenumber, validDate) AS (VALUES
  ('Alice', '704-555-1212', '2018-05-08'),
  ('Bob','804-555-1212', '2018-05-09')
) 
SELECT *
FROM phonebook2 p
WHERE EXISTS (
  SELECT *
  FROM cte c
  WHERE c.name = p.name AND c.validDate <= p.validDate
);

In the CTE you may include as many tuples as you want