UDB to SQL conversion
-
can anybody tell me the equilent SQL for this in sql server..
UPDATE CASH_CHECK A
SET A.COMPANY_ID =
(
SELECT CBP.COMPANY_ID
FROM CASH_PMT_ENTITY CPE, CASH_PMT_DETAIL CPD, CASH_BATCH_PMT CBP
WHERE CPE.N_CPD_OID = CPD.N_CPD_OID
AND CPD.N_CBP_OID = CBP.N_CBP_OID
AND CPE.C_OID_SYS_SRC IS NULL
AND CPE.C_OID_SYS IN ('CW')
AND A.N_CCHK_OID=CPE.N_CCHK_OID
)
WHERE EXISTS(SELECT CBP.COMPANY_ID
FROM CASH_PMT_ENTITY CPE, CASH_PMT_DETAIL CPD, CASH_BATCH_PMT CBP
WHERE CPE.N_CPD_OID = CPD.N_CPD_OID
AND CPD.N_CBP_OID = CBP.N_CBP_OID
AND CPE.C_OID_SYS_SRC IS NULL
AND CPE.C_OID_SYS IN ('CW')
AND A.N_CCHK_OID=CPE.N_CCHK_OID)
-
Have you tried running it? What error do you get?
It does not look very efficient, but it should run.
I wasn't born stupid - I had to study.
-
nope >> UPDATE CASH_CHECK A is forbiden.. the alias must be set in the from clause.
-
You can try:
UPDATE CASH_CHECK
SET CASH_CHECK.COMPANY_ID = CBP.COMPANY_ID
FROM CASH_CHECK
INNER JOIN CASH_PMT_ENTITY CPE ON CASH_CHECK.N_CCHK_OID=CPE.N_CCHK_OID
INNER JOIN CASH_PMT_DETAIL CPD ON CPE.N_CPD_OID = CPD.N_CPD_OID
INNER JOIN CASH_BATCH_PMT CBP ON CPD.N_CBP_OID = CBP.N_CBP_OID
AND CPE.C_OID_SYS_SRC IS NULL
WHERE CPE.C_OID_SYS IN ('CW')
Andy
-
Or, if you want to use aliases, you can write it like this (define alias in FROM, no table name or alias necessary for the column to be updated... it is always a column from the table named in UPDATE)
UPDATE a
SET COMPANY_ID = CBP.COMPANY_ID
FROM CASH_CHECK a
INNER JOIN CASH_PMT_ENTITY CPE ON a.N_CCHK_OID=CPE.N_CCHK_OID
INNER JOIN CASH_PMT_DETAIL CPD ON CPE.N_CPD_OID = CPD.N_CPD_OID
INNER JOIN CASH_BATCH_PMT CBP ON CPD.N_CBP_OID = CBP.N_CBP_OID
AND CPE.C_OID_SYS_SRC IS NULL
WHERE CPE.C_OID_SYS IN ('CW')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply