Hi Gad,
you can already do it with a MERGE... INTO..:
merge into TABLEA as A using (
select * from TABLEB
) as B on (A.ID = B.ID)
when matched then update
set V1 = B.V1,
V2 = B.V2
;;
Here's a working example:
-- Declare test table A
declare global temporary table TABLEA as (
select * from(values(1, 'A', 11), (2, 'B', 22), (3, 'C', 33)) x(ID, V1, V2)
) with data with replace
;;
-- Declare test table B
declare global temporary table TABLEB as (
select * from(values(7, 'X', 77), (2, 'Y', 42), (6, 'C', 66)) x(ID, V1, V2)
) with data with replace
;;
-- Merge values from B into A with a matching ID.
merge into TABLEA as A using (
select * from TABLEB
) as B on (A.ID = B.ID)
when matched then update
set V1 = B.V1,
V2 = B.V2
;;
Tim.
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Gad Miron <gadmiron@xxxxxxxxx>
Sent: 13 October 2020 21:48
To: midrange-l@xxxxxxxxxxxxxxxxxx <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RFE for SQL update from
Hello sages
Does anybody know of an RFE for SQL to enable a from clause in SQL update
statement ?
like the way it's done in MS SQL SERVER TSQL.
update file_A
set file_A.field1 = file_B.field1
from file_A inner join file_B on file_B.key = file_A.key
TIA
Gad
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
