• 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

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.PN = tbl_Inventory.Inventory_PartNumber AND (xx.version IS NOT NULL) AND inventory_ID = 118386 AND yy.Orderlines_Paid = 0
0
Chris Michalczuk
Asked:
Chris Michalczuk
  • 3
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update with join: here is my article about that:
http://www.experts-exchange.com/A_1517.html
0
 
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
0
 
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
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
bhess1,

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

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