Results 1 to 5 of 5

Thread: [RESOLVED] Select values from a different database in update but needed in query

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Resolved [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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Select values from a different database in update but needed in query

    Try like this?
    sql Code:
    1. UPDATE dbo.tblRSPBooking Set
    2.    R.CancelDate = T.TransactionDate,
    3.    R. IsCanceled = T.IsCancel
    4. FROM  dbo.tblRSPBooking R
    5. Inner JOIN (
    6.  select TramsactopmDate,  
    7.    from SERVER1.VSAPI.dbo.vwPartnerTransactions T
    8. join tblRSPBooking  R on T.MWTransactionID  = R.ReversalTransactionID ) T
    9. WHERE PartnerId = 1006
    10.       AND T.IsCancel = 1
    11.       AND T,TransactionDate >=  @LastCancel
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    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

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Select values from a different database in update but needed in query

    OK.
    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width