0

I am trying to write query which would insert data from one table to another table but only in case if last email history record holds different email value than it is in customers table.

I'm talking here about next scenario:

There is one table called customers and it looks like this:

Customer_Id
Customer_Email

There is second table called customers_email_history and it looks like this:

Customer_Email_History_Id
Customer_Id
Customer_Email

I would like to insert data from customers table which holds current customer_email value to table customer_email_history but only in case if customer_email from customers table is different than last record (newest record) related to that customer in customers_email_history. Here is an example:

SCENARIO 1: DO NOT INSERT DATA

As it is possible to see in table customers_email_history last row related to customer with id 1 is his current email from table customers so we wont insert new row in customers_email_history.

SCENARIO 2: INSERT DATA

enter image description here

As it is possbile to see for customer with id 2 we should insert new row to customers_email_history table since last (newest) row added related to that customer is not same as his current email from customers table. Customer table holds smith@john.com email while email history table holds smith1@john.com so we should insert smith@john.com to customers_email_history table

I tried to write something like this, but it is not working :(

SELECT T1.id, T1.email
FROM customers AS T1 INNER JOIN customers_email_history AS T2
on T1.id = T2.customer_id
WHERE T1.email != (SELECT T2.email FROM email_history ORDER BY ID DESC LIMIT 1) -- here i tried to get last (newest email) email related to that customer and to compare it 
with current email but this aint work liks this :(

SQL Fiddle

Josh Part
  • 2,154
  • 12
  • 15
Roxy'Pro
  • 4,216
  • 9
  • 40
  • 102

2 Answers2

1

Looks like a perfect case for EXCEPT ALL:

INSERT INTO customers_email_history(customer_id, email)
SELECT id, email
FROM   customers
EXCEPT ALL
(
SELECT DISTINCT ON (customer_id)
       customer_id, email
FROM   customers_email_history
ORDER  BY customer_id, id DESC
);

db<>fiddle here

See:

Assuming that customers_email_history.id really is an autoincrement column like serial or an IDENTITY column. See:

Else you need add a manual ID.

Depending on undisclosed Postgres version, cardinalities, table definition and data distribution, there may be (much) faster solutions. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • If I would like to add creation_date within insert statement I would do something like this: `INSERT INTO customers_email_history(customer_id, email, created_date)` and it says that I must add creation_date column also into the `EXCEPT_ALL` and I'm seeing more data in my select after I added this `creation_date`, why is that mate ? for example without `creation_date` there was like 200 rows and now it is like 250. Thanks Erwin mate! – Roxy'Pro Aug 10 '22 at 13:23
  • @Roxy'Pro: To add more columns that are not part of the uniqueness check, you need a different technique. Makes it a different question, really. Find techniques in the [linked answer](https://stackoverflow.com/a/19364694/939860), or ask a new question with the defining details – Erwin Brandstetter Aug 12 '22 at 22:03
0

Try this

SELECT T1.id, T1.email
FROM Customers AS T1
WHERE T1.email != (SELECT T2.email FROM Customers_Email_History AS T2 WHERE T1.Id = T2.Customer_ID ORDER BY ID DESC LIMIT 1)
Josh Part
  • 2,154
  • 12
  • 15