Update using a subquery

Dev Shed Forums Sponsor:

November 28th, 2012, 11:26 AM

martynhsmith

Registered User

Join Date: Nov 2012

Posts: 2

Time spent in forums: 12 m 58 sec
Reputation 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

November 28th, 2012, 02:19 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 30 m 23 sec
Reputation Power: 4140

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'

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

November 29th, 2012, 07:10 AM

martynhsmith

Registered User

Join Date: Nov 2012

Posts: 2

Time spent in forums: 12 m 58 sec
Reputation Power: 0

Quote:

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

Viewing: Dev Shed Forums > Databases > MS SQL Development > Update using a subquery

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump