Sql 2000 syntax issue with updates and joins

Posted on 2011-10-14
Last Modified: 2012-05-12
trying to update a field as shown below but got all my joins wrong in this syntax!!!
also how would I add a variable into the set statement which picks up today's date in uk format
eg set field  = '13/10/2011 - test'   i want the 13/10/2011 to change based on today's date

UPDATE    tbl_Inventory
SET              tbl_Inventory.Inventory_ManufacturerSN = '13/10/2011 - test'  
FROM         triscan_licencing.dbo.OdysseyPartsMasterView xx

                      tbl_Jobs ON tbl_Inventory.Inventory_ID = tbl_Jobs.Jobs_ParentInventoryID INNER JOIN
                      triscan_orders.dbo.tbl_OrderLines yy ON yy.OrderLines_ID = tbl_Inventory.Inventory_OrderLineId
WHERE     xx.PN = tbl_Inventory.Inventory_PartNumber AND (xx.version IS NOT NULL) AND inventory_ID = 118386 AND yy.Orderlines_Paid = 0
Question by:chrismichalczuk
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    update with join: here is my article about that:
    LVL 142

    Accepted Solution

    apart from that:
    SET              tbl_Inventory.Inventory_ManufacturerSN = convert(varchar(10), getdate(), 103 ) + ' - test'  

    date and time article:
    LVL 32

    Assisted Solution

    Assuming that tbl_Inventory is not actually  triscan_licencing.dbo.OdysseyPartsMasterView, then try including that in the FROM list, e.g.

    UPDATE tbl_Inventory
    SET tbl_Inventory.Inventory_ManufacturerSN = '13/10/2011 - test'
    FROM tbl_Inventory  
    INNER JOIN triscan_licencing.dbo.OdysseyPartsMasterView xx
          ON xx.PN = tbl_Inventory.Inventory_PartNumber
    INNER JOIN tbl_Jobs
          ON tbl_Inventory.Inventory_ID = tbl_Jobs.Jobs_ParentInventoryID
    INNER JOIN triscan_orders.dbo.tbl_OrderLines yy
          ON yy.OrderLines_ID = tbl_Inventory.Inventory_OrderLineId
    WHERE xx.version IS NOT NULL
          AND inventory_ID = 118386
          AND yy.Orderlines_Paid = 0
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]

     I found that actually, the table you update does not need to be in the from list ...

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now