#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    Update using a subquery


    I have this SQL

    Code:
    select zero.ftr_dbtc, zero.ftr_tref,
    (select [InvoiceNumber] from  [InterfaceLive].[dbo].[SITS_FeesImport] datlines where
    zero.ftr_pstd=datlines.ftr_pstd and left(zero.ftr_scjc,7)= datlines.[ftr_dbtc]) as invoicenumber
    from SITS_FeesImport_ZeroInv zero where BursaryType='Textline'
    it uses two tables, I use values in feesimport_zeroinv table to get the value of invoicenumber from the feesimport table

    It works but what I really want to do is update the invoicenumber in feesimport_zeroinv but I can't get it to work

    Something like this (this doesn't work)
    Code:
    update SITS_FeesImport_ZeroInv zero     
    set [InvoiceNumber]= (select [InvoiceNumber] from  [InterfaceLive].[dbo].[SITS_FeesImport] datlines where zero.ftr_pstd=datlines.ftr_pstd and left(zero.ftr_scjc,7)= datlines.[ftr_dbtc]) 
    where BursaryType='Textline'
    Any help most appreciated

    M
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Code:
    UPDATE SITS_FeesImport_ZeroInv      
       SET InvoiceNumber = datlines.InvoiceNumber
      FROM SITS_FeesImport_ZeroInv AS zero
    INNER
      JOIN InterfaceLive.dbo.SITS_FeesImport AS datlines 
        ON datlines.ftr_pstd = zero.ftr_pstd
       AND datlines.ftr_dbtc = LEFT(zero.ftr_scjc,7) 
     WHERE zero.BursaryType = 'Textline'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    Code:
    UPDATE SITS_FeesImport_ZeroInv      
       SET InvoiceNumber = datlines.InvoiceNumber
      FROM SITS_FeesImport_ZeroInv AS zero
    INNER
      JOIN InterfaceLive.dbo.SITS_FeesImport AS datlines 
        ON datlines.ftr_pstd = zero.ftr_pstd
       AND datlines.ftr_dbtc = LEFT(zero.ftr_scjc,7) 
     WHERE zero.BursaryType = 'Textline'
    Thank you, that works perfectly

IMN logo majestic logo threadwatch logo seochat tools logo