CODEWITHSUNDEEP
sql-server
Taryn
Taryn

Reputation: 247720

SQL Update Table By Joining Same Table

I am working on an MS Access DB rewrite project that we are converting to a 2005 SQL server back-end/Winform front-end. I have a query in Access that I need to convert to run in SQL server. Here is the Access query:

UPDATE DISTINCTROW VAR, CancelsinTrans 
INNER JOIN Trans ON (CancelsinTrans.ACCT = Trans.ACCT) AND (CancelsinTrans.ITEM_ID = Trans.ITEM_ID) 
SET Trans.TRAN_CD = "1", Trans.LastUpdated = Date() 
WHERE (((Trans.TRAN_CD)<>"1" Or (Trans.TRAN_CD) Is Null) AND 
((CancelsinTrans.TRAN_DT)<[VAR]![Import1]) AND 
((Trans.TRAN_DT)<[VAR]![Import1]));

The CancelsinTrans is a query that is pulling accounts from the Trans table - so basically it is joining on itself and performing an update.

Here is the CancelsInTrans query:

SELECT Trans.ACCT, Trans.TRAN_TYPE_CD, Trans.TRAN_SUBOR_CD, Trans.M_SRCE_CD, Trans.TRAN_RQST_CD, Trans.TRAN_AM, Trans.TRAN_DR_CR_CD, Trans.TRAN_CXL_CD, Trans.ACCTG_CD, Trans.ITEM_ID, Trans.TRAN_DT, Trans.TRAN_EXEC_TM, Trans.TRAN_QY, Trans.TRAN_NET, Trans.TRAN_EXEC_PR, Trans.M_SECURITY_NR, Trans.M_ORF_OR_ORDRNO, Trans.M_SEQ_NBR, Trans.TRAN_SETTL, Trans.M_ORDER_TAKER_ID, Trans.QUOTR_SEC, Trans.PROD_CD, Trans.SEC_CD, Trans.TRAN_EXEC_DT
FROM Trans
WHERE (((Trans.TRAN_TYPE_CD)="TR") AND ((Trans.TRAN_SUBOR_CD)="TR") AND ((Trans.TRAN_CD)="1") AND ((Trans.ACCTG_CD)="1"));

I am trying to figure out the best way to write this query. I was going to use a stored proc to run this update but should I use a table-function to get the set of records.

Any help would be great.

Thanks

Upvotes: 1

Views: 2517

Answers (1)

Thomas
Thomas

Reputation: 64645

The SQL Server equivalent would be something like the following:

Update Trans
Set TRAN_CD = '1'
    , LastUpdated = GETDATE()
Where ( Trans.TRAN_CD <> '1' Or Trans.TRAN_CD Is Null )
    And Trans.TRAN_DT < 'some var value'
    And Exists  (
                Select 1
                From CancelsinTrans As C1
                Where C1.ACCT= Trans.ACCT
                    And C1.ITEM_ID = Trans.ITEM_ID
                    And C1.TRAN_DT < 'some var value'
                )

In this case, through your parameterized query, you would replace some var value with the value from [VAR]![Import1] which I presume was a form value.

UPDATE

Given that the CancelsInTran query encapsulates the Trans table, you can eliminate the join like so:

Update Trans
Set TRAN_CD = '1'
    , LastUpdated = GetDate()
Where TRAN_TYPE_CD = 'TR'
    And TRAN_SUBOR_CD = 'TR'
    And ACCTG_CD = '1'
    And ( TRAN_CD <> '1' Or TRAN_CD Is Null )
    And TRAN_DT < 'some value'

Upvotes: 2

Related Questions