I have two tables (see below) in a postgreSQL database.
Table1
tracks all changes made to each ID (i.e. multiple rows per ID):
create table table1(id,record,name,data1,data2,data3)as values
(1,1,'Dave', 1.2,55,1)
,(1,2,'David', 1.2,55,1)
,(2,1,'Sam', .8,30,2)
,(1,1,'Jenn', .9,28,2)
,(1,1,'Arthur',1.1,77,1)
,(1,1,'Jim', .7,42,2)
,(1,1,'Jimmy', .7,42,2)
,(1,1,'James', .7,42,2)
,(1,1,'Sue', 1.3,32,2)
,(1,1,'Susan', 1.3,32,2);
Table2
contains only one row per ID:
create table table2(id,record,name,data1,data2,data3)as values
(1,1,'Dave', 1.2,55,1)
,(2,1,'Sam', .8,30,2)
,(3,1,'Jenn', .9,28,2)
,(4,1,'Arthur',1.1,77,1)
,(5,2,'Jimmy', .7,42,2)
,(6,1,'Sue', 1.3,32,2);
I need an SQL query to update Table2
based on the highest Record value for each ID in Table1
and also updating all other fields as well (data1
, data2
, data3
in this example).
These tables are just examples but my actual data has many columns, so I would like to use some sort of wild card to include all fields in the update.
Relatively new to SQL in postgreSQL but here is my attempt at getting the most recent record version returned, but not sure how to update Table2 from there. I have had a few attempts but nothing work so probably not worth posting that code.
select * from
Table1 t1
where
(ID,Record) in
(
select
ID,
MAX(Record)
from
Table1 t1
group by
ID
)
id | record | name | data1 | data2 | data3 |
---|---|---|---|---|---|
1 | 2 | David | 1.2 | 55 | 1 |
2 | 1 | Sam | 0.8 | 30 | 2 |
ExpectedResult:
You need to sign in to view this answers