Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: Update query question

    Hi,

    I really hope you can help. I have what seems like a simple request, but I am struggling

    So, I have 4 tables, I need to update the month and year columns on tblDirect with the month and year values from tblNewData. Sounds straight forward enough but the problem is that there no relation between tblDirect and tblNewData; the relationship is created be joining three tables (tblDirect, tblPOLine, tblPurchaseOrder) as the unique ref is created by concatenating tblPurchaseOrder.tPONumber and tblPOLine.iLineNumber) - this is then joined directly to the tblNewData.unique_ref.

    The tables and columns:
    tblDirect (month, year, rowid)
    tblPOLine (ipoid, iLineNumber)
    tblPurchaseOrder (iid, tPONumber)
    tblNewData (unique_ref, month, year)

    Relations:
    tblDirect.rowid - tblPOLine.ipoid
    tblPOLine.ipoid - tblPurchaseOrder.iid

    So I need to end up with something like:

    update tblUpate
    set month = (select month from tblNewData where ~ problem here, I need the composite key ~ = tblNewData.unique_ref),
    year = (select year from tblNewData where ~ problem here, I need the composite key ~ = tblNewData.unique_ref)

    Any help would be greatly appreciated.

    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    update tblDirect
    set month = 
    		(select tblNewData.month 
    		from tblPOLine
    			INNER JOIN tblPurchaseOrder ON
    				tblPOLine.ipoid = tblPurchaseOrder.iid
    			INNER JOIN tblNewData ON
    				tblPurchaseOrder.tPONumber + tblPOLine.iLineNumber = tblNewData.unique_ref
    		WHERE tblDirect.rowid = tblPOLine.ipoid
    		)
    year = (select tblNewData.year 
    		from <same as above>
    		)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    Hi,

    Thanks for the response; I have actually got it working now, I used something similar to:

    UPDATE u
    SET u.month=n.month,
    u.year=n.year
    FROM tblDirect u
    JOIN tblPOLine l
    ON u.rowid=i.ipoid
    JOIN tblPurchaseOrder p
    ON p.iid = l.ipoid
    JOIN tblNewData n
    ON n.unique_ref = p.tPONumber + l.iLineNumber

    I'm not sure if there is a difference between using "month = (select field from table..." or month=n.month?

    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
  •