• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Sql 2000 syntax issue with updates and joins

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
Chris Michalczuk
Chris Michalczuk
  • 3
2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update with join: here is my article about that:
Guy Hengel [angelIII / a3]Billing EngineerCommented:
apart from that:
SET              tbl_Inventory.Inventory_ManufacturerSN = convert(varchar(10), getdate(), 103 ) + ' - test'  

date and time article: http://www.experts-exchange.com/A_1499.html
Brendt HessSenior DBACommented:
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
      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
Guy Hengel [angelIII / a3]Billing EngineerCommented:

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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now