July 23, 2014 at 4:54 am
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,
July 23, 2014 at 5:28 am
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)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply