-
Apr 26th, 2017, 07:53 AM
#1
[RESOLVED] Select values from a different database in update but needed in query
Code:
-- UPDATE dbo.tblRSPBooking
-- SET CancelDate = vwPartnerTransactions.TransactionDate,
-- IsCanceled = vwPartnerTransactions.IsCancel
select *
FROM SERVER1.VSAPI.dbo.vwPartnerTransactions
WHERE PartnerId = 1006
AND IsCancel = 1
AND TransactionDate >= @LastCancel
AND tblRSPBooking.MWTransactionID = ReversalTransactionID
I need to do a select on this one.
I get The multi-part identifier "tblRSPBooking.MWTransactionID" could not be bound.
on the AND tblRSPBooking.MWTransactionID = ReversalTransactionID
Is it safe to assume that in order to do a select i need to join MWTransactionID and ReversalTransactionID
like
Code:
select ......etc
from SERVER1.VSAPI.dbo.vwPartnerTransactions T
join tblRSPBooking R on T.MWTransactionID = R.ReversalTransactionID
?
Thanks.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Apr 26th, 2017, 09:52 AM
#2
Re: Select values from a different database in update but needed in query
Try like this?
sql Code:
UPDATE dbo.tblRSPBooking Set R.CancelDate = T.TransactionDate, R. IsCanceled = T.IsCancel FROM dbo.tblRSPBooking R Inner JOIN ( select TramsactopmDate, from SERVER1.VSAPI.dbo.vwPartnerTransactions T join tblRSPBooking R on T.MWTransactionID = R.ReversalTransactionID ) T WHERE PartnerId = 1006 AND T.IsCancel = 1 AND T,TransactionDate >= @LastCancel
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Apr 26th, 2017, 10:00 AM
#3
Re: Select values from a different database in update but needed in query
Hi Gary.
The update will work fine as I've posted in the first thread (commented out but is meant to be uncommented), the problem is when I do a select to see what the update will actually do.
Then is when I get the error, that seems logical as I do not have a join on the 2 tables (in contrasts with the update that has the first table in the update part and the second in the from part).
So I'm wondering how will go about to "selectize" the update.
(Will see this tomorrow as we have a meeting in a few minutes)
Thanks.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Apr 26th, 2017, 10:15 AM
#4
Re: Select values from a different database in update but needed in query
Without the join or the ReversalTransactionID I am not sure you can.
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Apr 27th, 2017, 02:01 AM
#5
Re: Select values from a different database in update but needed in query
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|