Update from another table using substring..

  • Hi,

    I have a table BankRef with a BankID column that was populated from the first 24 characters of a column TransDesc from another table BankTransactions.

    I wish to repopulate the BankID column from the TransDesc column again but using the first 26 characters. The BankID column also has another column OID which has had data manually entered so I don't want to lose the current BankID/OID combination, so can't just re populate the BankID col with the 26 character data.

    I tried something like this..

    WITH CTE AS

    (

    SELECT BT.TRANSDESC, BR.BANKID

    FROM EPSBANKTRANSACTIONS BT, EPSBANKREF BR

    WHERE SUBSTRING(BT.TRANSDESC, 0,25) = BR.BANKID

    )

    UPDATE EPSBANKREF

    SET BANKID

    SELECT SUBSTRING(M.TRANSDESC,0,27)

    FROM CTE M

    WHERE M.BANKID = SUBSTRING(M.TRANSDESC, 0,25)

    but get incorrect syntax near SELECT (the second one)

    thanks,

  • mattech06 (7/23/2014)


    Hi,

    I have a table BankRef with a BankID column that was populated from the first 24 characters of a column TransDesc from another table BankTransactions.

    I wish to repopulate the BankID column from the TransDesc column again but using the first 26 characters. The BankID column also has another column OID which has had data manually entered so I don't want to lose the current BankID/OID combination, so can't just re populate the BankID col with the 26 character data.

    I tried something like this..

    WITH CTE AS

    (

    SELECT BT.TRANSDESC, BR.BANKID

    FROM EPSBANKTRANSACTIONS BT, EPSBANKREF BR

    WHERE SUBSTRING(BT.TRANSDESC, 0,25) = BR.BANKID

    )

    UPDATE EPSBANKREF

    SET BANKID

    SELECT SUBSTRING(M.TRANSDESC,0,27)

    FROM CTE M

    WHERE M.BANKID = SUBSTRING(M.TRANSDESC, 0,25)

    but get incorrect syntax near SELECT (the second one)

    thanks,

    You can't use use a SELECT in an UPDATE statement unless it's a derived query as part of a JOIN. What you want to do is JOIN the CTE to the EPSBankRef table.

    Since I don't know the structure of your tables, I can't recommend an exact JOIN statement but I'll give you my best guess.

    WITH CTE AS

    (

    SELECT BT.TRANSDESC, BR.BANKID

    FROM EPSBANKTRANSACTIONS BT

    INNER JOIN EPSBANKREF BR

    ON SUBSTRING(BT.TRANSDESC, 0,25) = BR.BANKID

    --I believe the old style joins are deprecated, so don't use them.

    )

    UPDATE eps

    SET BANKID = SUBSTRING(M.TRANSDESC,0,27)

    FROM EPSBANKREF eps

    INNER JOIN CTE M

    ON eps.(matching column) = m.(matching column)

    WHERE M.BANKID = SUBSTRING(M.TRANSDESC, 0,25)

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply