0

I have two tables
table1:

|  id |  A   | B   | C    |    date1     |    date2     |
|-----|------|-----|------|--------------|--------------|
|  1  | 100  | 100 |  100 | 10-Jan-2019  |  11-Jan-2019 |
|  2  | 100  | 100 |  100 | 10-Jan-2019  |  12-Jan-2019 |
|  3  | 200  | 200 |  100 | 10-Jan-2019  |  13-Jan-2019 |

table2:

|  id |  A   | B   | C    |        date1         | date2 |
|-----|------|-----|------|----------------------|-------|
|  x  | 100  | 100 |  100 | 10-Jan-2019 08:00:00 |       |
|  y  | 100  | 100 |  100 | 10-Jan-2019 07:00:00 |       |
|  z  | 200  | 200 |  100 | 10-Jan-2019 03:00:00 |       |

I want to update the date2 column in the table2 by matching on column A,B,C of table1 and table2.

I have this query

with matched_id (id) as (
update table2 t2 
set t2.date2 = t1.date1
from table1 t1 
where t1.a=t2.a AND t1.b= t2.b  
and t1.c = t2.c
and tochar(t1.date1, 'dd-mm-yyyy') = tochar(t2.date1, 'dd-mm-yyyy')
and t2.date2 is null
Returning t1.id 
)
Delete table1 where id in (select id from matched_id);

Right now the date2 filed is getting updated with 11-Jan for both id x,y in table 2. I want to update the records to date2 as 11-Jan and 12-Jan.
In other words if one record from table2 is matched with a record in table 1, it should not match with other records in table1.

Is there a way to achieve this in a single query?

  • its PostgreSQL 9.6 – mahesh gupta Feb 18 at 15:44
  • There are two rows in table1 with same A,B,C (and date1); (100,100,100, '10-Jan-2019'). How should a row be chosen between the two in that case? In other words, why should the row with id=x be updated from the row with id=1 (and the y from 2) and not the x from 2 and the y from 1? – ypercubeᵀᴹ Feb 18 at 16:56
  • What should happen if there are 17 rows in table2 and 5 rows in table1, all with same A,B,C? – ypercubeᵀᴹ Feb 18 at 17:00
  • the order does not matter, what I want to achieve is that each row in table2 should be updated with the date2 of corresponding record from table1. if there is two in table1 with different date2 value, then those two should update two different records in table2 not the same records. – mahesh gupta Feb 18 at 17:29
  • 1
    @maheshgupta - please answer ypercube's question about row count differences between table1 and table2, all with the same A, B, C values - how should the update be done? – Scott Hodgin Feb 18 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.