Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE stg (
	folder varchar NULL,
	subject_id numeric NULL,
	wf_id numeric NULL,
	instance_id numeric NULL,
	workflow_name varchar NULL,
	session_id numeric NULL,
	session_name varchar NULL,
	mapping_name varchar NULL,
	insert_date timestamp NULL DEFAULT now(),
	update_date timestamp NULL DEFAULT now()
);

CREATE TABLE


INSERT INTO stg
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('a', 457, 305783, 213606, 'b', 305781, 'c', 'f', NULL, '2024-01-28 13:04:08.000');
INSERT INTO stg
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('a', 457, 305783, 213608, 'b', 305780, 'd', 'g', NULL, '2024-01-28 13:04:08.000');
INSERT INTO stg
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('a', 457, 305783, 213759, 'b', 306008, 'e', 'h', NULL, '2024-01-28 13:04:08.000');
INSERT INTO stg
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('l', 443, 287502, 198518, 'j', 287456, 'r', NULL, NULL, '2024-01-28 13:04:09.000');
INSERT INTO stg
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('o', 234, 305521, 213376, 'x', 305519, 'z', 'p', NULL, '2024-01-28 13:04:09.000');
INSERT INTO stg
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('o', 234, 305521, 213376, 'x', 305519, 'z', 'p', NULL, '2024-01-28 13:04:09.000');

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select *From stg


folder
subject_id
wf_id
instance_id
workflow_name
session_id
session_name
mapping_name
insert_date
update_date


a
457
305783
213606
b
305781
c
f
null
2024-01-28 13:04:08

a
457
305783
213608
b
305780
d
g
null
2024-01-28 13:04:08

a
457
305783
213759
b
306008
e
h
null
2024-01-28 13:04:08

l
443
287502
198518
j
287456
r
null
null
2024-01-28 13:04:09

o
234
305521
213376
x
305519
z
p
null
2024-01-28 13:04:09

o
234
305521
213376
x
305519
z
p
null
2024-01-28 13:04:09



SELECT 6


CREATE TABLE main (
	folder varchar NULL,
	subject_id numeric NULL,
	wf_id numeric NULL,
	instance_id numeric NULL,
	workflow_name varchar NULL,
	session_id numeric NULL,
	session_name varchar NULL,
	mapping_name varchar NULL,
	insert_date timestamp NULL DEFAULT now(),
	update_date timestamp NULL DEFAULT now()
);

CREATE TABLE


INSERT INTO main
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('a', 457, 305783, 213606, 'b', 305781, 'c', 'f', NULL, '2024-01-28 13:04:08.000');
INSERT INTO main
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('a', 457, 305783, 213608, 'b', 305780, 'd', 'g', NULL, '2024-01-28 13:04:08.000');
INSERT INTO main
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('a', 457, 305783, 213759, 'b', 306008, 'e', 'h', NULL, '2024-01-28 13:04:08.000');
INSERT INTO main
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('a', 457, 305783, 213759, 'b', 306008, 'e', 'h', NULL, '2024-01-28 13:04:08.000');
INSERT INTO main
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('l', 443, 287502, 198518, 'j', 287456, 'r', NULL, NULL, '2024-01-28 13:04:09.000');
INSERT INTO main
(folder, subject_id, wf_id, instance_id, workflow_name, session_id, session_name, mapping_name, insert_date, update_date)
VALUES('o', 234, 305521, 213376, 'x', 305519, 'z', 'p', NULL, '2024-01-28 13:04:09.000');

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select *from main


folder
subject_id
wf_id
instance_id
workflow_name
session_id
session_name
mapping_name
insert_date
update_date


a
457
305783
213606
b
305781
c
f
null
2024-01-28 13:04:08

a
457
305783
213608
b
305780
d
g
null
2024-01-28 13:04:08

a
457
305783
213759
b
306008
e
h
null
2024-01-28 13:04:08

a
457
305783
213759
b
306008
e
h
null
2024-01-28 13:04:08

l
443
287502
198518
j
287456
r
null
null
2024-01-28 13:04:09

o
234
305521
213376
x
305519
z
p
null
2024-01-28 13:04:09



SELECT 6


UPDATE main t1 SET folder=s.folder,workflow_name=s.workflow_name,session_name=s.session_name,mapping_name=s.mapping_name,update_date=to_char(now(),'yyyy-mm-dd hh24:mi:ss')::timestamp 
FROM (select distinct folder,workflow_name,session_name,mapping_name from stg)s1
 WHERE t1.subject_id=s1.subject_id and t1.wf_id=s1.wf_id and t1.instance_id=s1.instance_id and t1.session_id=s1.session_id and s1.insert_date is null

ERROR:  column s1.subject_id does not exist
LINE 3:  WHERE t1.subject_id=s1.subject_id and t1.wf_id=s1.wf_id and...
                             ^
HINT:  Perhaps you meant to reference the column "t1.subject_id".
folder	subject_id	wf_id	instance_id	workflow_name	session_id	session_name	mapping_name	insert_date	update_date
a	457	305783	213606	b	305781	c	f	null	2024-01-28 13:04:08
a	457	305783	213608	b	305780	d	g	null	2024-01-28 13:04:08
a	457	305783	213759	b	306008	e	h	null	2024-01-28 13:04:08
l	443	287502	198518	j	287456	r	null	null	2024-01-28 13:04:09
o	234	305521	213376	x	305519	z	p	null	2024-01-28 13:04:09
o	234	305521	213376	x	305519	z	p	null	2024-01-28 13:04:09