April 24, 2017 at 8:55 am
I have TSQL UPDATE statement yielding the error "multi-part identifier could not be found". The statement is slightly complex, having both multiple INNER JOINS and referencing the *same* table, aliased differently in each join. Oddly, the SELECT version of this statement works fine. The UPDATE version of the statement, however, is giving me the error.
What I'm trying to do is net two field values (BalanceOutstanding) from different table rows in MasterLoandata. One row has +value and the other row a -value. I want to net the two values into alias M1 row and set what was the M2 -value to 0 after netting. I've used this same technique successfully in MySQL, FWIW.
Here's the SELECT statement used for my sanity check, which works fine.
SELECT m1.notenumber, m1.balanceoutstanding, m2.notenumber, m2.balanceoutstanding, m1.balanceoutstanding + m2.balanceoutstanding as NetBal FROM RelatedParticipationLoans AS r
INNER JOIN MasterLoandata AS m1 ON r.RelatedNotenumber = m1.Notenumber AND m1.bankid = 508 and m1.clientid = 441 AND m1.import_asofdate = '2012-01-31'
INNER JOIN MasterLoandata AS m2 ON r.SoldNotenumber = m2.Notenumber and m2.bankid = 508 AND m2.clientid = 441 AND m2.import_asofdate = '2012-01-31'
WHERE NOT m1.balanceoutstanding + m2.balanceoutstanding < 0
Now, I want to convert this same join logic to an UPDATE statement:
UPDATE r SET m1.BalanceOutstanding = m1.BalanceOutstanding + m2.BalanceOutstanding, m2.BalanceOutstanding = 0, m2.Loanstatus = 'X'
from RelatedParticipationLoans AS r
INNER JOIN MasterLoandata AS m1 ON r.RelatedNotenumber = m1.Notenumber AND m1.bankid = 508 AND m1.clientid = 441 AND m1.import_asofdate = '2012-01-31'
INNER JOIN MasterLoandata AS m2 ON r.SoldNotenumber = m2.Notenumber AND m2.bankid = 508 AND m2.clientid = 441 AND m2.import_asofdate = '2012-01-31'
WHERE NOT m1.balanceoutstanding + m2.balanceoutstanding < 0
This update yields an error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "m1.BalanceOutstanding" could not be bound.
So what am I doing wrong here?
TIA,
Rick
April 24, 2017 at 9:01 am
you're telling it to update 'r' yet the 'set' statements reference aliases 'm1' and 'm2'. That doesn't look like its going to work.
April 24, 2017 at 9:02 am
Your UPDATE statement has a single target (r)
Yet your statement is attempting to update columns from m1 and m2.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 24, 2017 at 9:13 am
Let's back up a second then. I want to translate the SELECT shown above to an UPDATE statement. I want to update m1 balanceOutstanding expressed as:
-- add +value row to -value row for NetBalance
m1.BalanceOutstanding = m1.balanceoutstanding + m2.balanceoutstanding
-- and
m2.BalanceOutstanding = 0
Can anyone help with that? This stuff works in MySQL, and I'm trying to make it work SQL Server.
Here's the SELECT again:
SELECT m1.notenumber, m1.balanceoutstanding, m2.notenumber, m2.balanceoutstanding, m1.balanceoutstanding + m2.balanceoutstanding as NetBal FROM RelatedParticipationLoans AS r
INNER JOIN MasterLoandata AS m1 ON r.RelatedNotenumber = m1.Notenumber AND m1.bankid = 508 and m1.clientid = 441 AND m1.import_asofdate = '2012-01-31'
INNER JOIN MasterLoandata AS m2 ON r.SoldNotenumber = m2.Notenumber and m2.bankid = 508 AND m2.clientid = 441 AND m2.import_asofdate = '2012-01-31'
WHERE NOT m1.balanceoutstanding + m2.balanceoutstanding < 0
April 24, 2017 at 9:37 am
I'm thinking that statement will not translate into an update directly on Microsoft SQL Server. I think MySQL is probably more agreeable about that sort of update but I don't use MySQL.
April 24, 2017 at 9:41 am
rick.duke - Monday, April 24, 2017 9:13 AMLet's back up a second then. I want to translate the SELECT shown above to an UPDATE statement. I want to update m1 balanceOutstanding expressed as:-- add +value row to -value row for NetBalance
m1.BalanceOutstanding = m1.balanceoutstanding + m2.balanceoutstanding
-- and
m2.BalanceOutstanding = 0Can anyone help with that? This stuff works in MySQL, and I'm trying to make it work SQL Server.
Here's the SELECT again:
SELECT m1.notenumber, m1.balanceoutstanding, m2.notenumber, m2.balanceoutstanding, m1.balanceoutstanding + m2.balanceoutstanding as NetBal FROM RelatedParticipationLoans AS r
INNER JOIN MasterLoandata AS m1 ON r.RelatedNotenumber = m1.Notenumber AND m1.bankid = 508 and m1.clientid = 441 AND m1.import_asofdate = '2012-01-31'
INNER JOIN MasterLoandata AS m2 ON r.SoldNotenumber = m2.Notenumber and m2.bankid = 508 AND m2.clientid = 441 AND m2.import_asofdate = '2012-01-31'
WHERE NOT m1.balanceoutstanding + m2.balanceoutstanding < 0
Please try again to explain what you are trying to achieve. I do not understand how you can update BalanceOutstanding to two different things simultaneously – we'll have to find alternative syntax to make this work in T-SQL.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 24, 2017 at 10:08 am
Thanks for hanging on. I'm trying to update two different (related) rows in a single table, MasterLoandata, each update row referenced as alias M1 and alias M2. The two related rows, M1 and M2 are created by an inner join with another table -- alias R
Assume these rows in table Master
-- fieldnames: pkID, Notenumber, Balance
1, 'Note1', 500
... other unrelated records in between, pkID 2 through 9 rows --> Note2 through Note9
10, 'Note10', -100
Another table, RelatedLoans, defines a relationship between loans, Note1 and Note10 in the above table:
-- fieldnames: id, MasterLoan, SoldLoan
1, 'Note1', 'Note10'
2, 'Note2', 'Note9'
3. 'Note3', 'Note8'
Objective: Net two balances for Note1 and Note10 into a single value into one of the related rows -- the Note1 row with a +value
After netting the values, remove the -value from Note10 row.
After the update, Master table looks like:
-- fields pkID, Notenumber, Balance
1, 'Note1', 400
... other unrelated records in between, pkID 2 through 9 -- Note2 through Note9
10, 'Note10', 0
The table SUM(Balance) both before and after the update is unchanged (sum value 400), but balance of Note1 and Note10 both have been modified.
Any clearer?
April 24, 2017 at 10:18 am
Just posting for fun, I highly recommend you follow good programming practices including testing all solutions using adequate test code coverage especially if this is real money you're dealing with. Don't be one of those guys who plugs in code from the web without understanding fully what you're dealing with. Additionally, the following is only a post for generating discussion on producing an update statement that would fit this approximate use case, this code is not intended for cut and past style programming or even to actually be run on your computer. You should study SQL adequately including all of your vendors requirements for composing safe, accurate, and bug free programming text to run on your systems.
UPDATE m SET m.BalanceOutstanding =
case
when m.Notenumber = r.RelatedNotenumber then
m.BalanceOutstanding + (select m2.BalanceOutstanding from MasterLoanData where m2.Notenumber =
(select r.SoldNotenumber from RelatedParticipationLoans as r where
r.RelatedNotenumber = m1.notenumber)
)
when m.Notenumber = r.SoldNotenumber then
0
end,
m.Loanstatus =
case when m.Notenumber = r.RelatedNotenumber then m.Loanstatus
when m.Notenumber = r.SoldNotenumber then 'X'
end
from MasterLoandata m
join RelatedParticipationLoans r
on m.Notenumber = r.RelatedNotenumber or m.Notenumber = r.SoldNotenumber
Again, all disclaimers apply, I'm just posting this for discussion sake and to practice my typing!
April 24, 2017 at 10:45 am
Have a look at this. It assumes that RelatedLoans.SoldLoan cannot also exist in the MasterLoan column:
DECLARE @MasterLoanData TABLE
(
NoteNumber VARCHAR(10)
, Balance INT
);
DECLARE @RelatedLoans TABLE
(
MasterLoan VARCHAR(10)
, SoldLoan VARCHAR(10)
);
INSERT @MasterLoanData
(
NoteNumber
, Balance
)
VALUES
(
'Note1'
,500
)
,(
'Note10', -100
);
INSERT @RelatedLoans
(
MasterLoan
, SoldLoan
)
VALUES
(
'Note1'
,'Note10'
)
,(
'Note2', 'Note9'
);
SELECT *
FROM @MasterLoanData mld;
SELECT *
FROM @RelatedLoans rl;
UPDATE mld
SET mld.Balance = CASE
WHEN mld2.NoteNumber IS NULL THEN
0
ELSE
mld.Balance + mld2.Balance
END
FROM
@MasterLoanData mld
LEFT JOIN @RelatedLoans rl ON mld.NoteNumber = rl.MasterLoan
LEFT JOIN @MasterLoanData mld2 ON rl.SoldLoan = mld2.NoteNumber;
SELECT *
FROM @MasterLoanData mld;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 24, 2017 at 1:08 pm
Thanks both of you. I'll play around with it.
Rick
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply