Joins in update
This is a multi-part message in MIME format.
------=_NextPart_000_004A_01BF0055.6D9C1930
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi everybody,
I have this Update statement which looks like this:
UPDATE t_ldc_aps SET acct_id =3D (SELECT acct_id FROM t_tsys_acct WHERE =t_ldc_aps.crdt_acct_id =3D t_tsys_acct.crdt_acct_id) WHERE crdt_acct_id =
IN (SELECT DISTINCT crdt_acct_id FROM t_tsys_acct) AND WHERE acct_id =3D =
0;
t_ldc_aps and t_tsys_acct has 50,000+ and 6million+ records =
respectively, and this process is a bit slow.
Is there any way I can use joins instead of subquerys in an Update =
statement? Also what would be a good way of avoiding a long transaction =
in this SQL?=20
I thought about cursors but those would also take a lot of time. ( =
Checking each record in t_ldc_aps against millions in t_tsys_acct =
doesn't sound very appealing)
Another idea was batch updates, but we do not have a serial column for =
t_ldc_aps, so I'm in a fix about that.
Thanks,
George
------=_NextPart_000_004A_01BF0055.6D9C1930
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2014.210" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi everybody,</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>I have this Update statement which =
looks like=20
this:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>UPDATE t_ldc_aps SET acct_id =3D =
(SELECT acct_id FROM=20
t_tsys_acct WHERE t_ldc_aps.crdt_acct_id =3D t_tsys_acct.crdt_acct_id) =
WHERE=20
crdt_acct_id IN (SELECT DISTINCT crdt_acct_id FROM t_tsys_acct) AND =
WHERE=20
acct_id =3D 0;</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>t_ldc_aps and t_tsys_acct has 50,000+ =
and 6million+=20
records respectively, and this process is a bit slow.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Is there any way I can use joins =
instead=20
of subquerys in an Update statement? Also what would be a good way =
of=20
avoiding a long transaction in this SQL? </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I thought about cursors but those would =
also take a=20
lot of time. ( Checking each record in t_ldc_aps against millions in =
t_tsys_acct=20
doesn't sound very appealing)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Another idea was batch updates, =
but we do not=20
have a serial column for t_ldc_aps, so I'm in a fix about=20
that.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>George</FONT></DIV></BODY></HTML>
------=_NextPart_000_004A_01BF0055.6D9C1930--