Error to convert Query to Procedure

[ahhuang]

Hi,
I am upgrading from Access to MSQL database but i am unable to convert the following query into procedure.
Please advice on how this statment can be amended to be accepted by MSQL
Thanx

UPDATE [INV Items] INNER JOIN [INV Item Location] ON [INV Items].[INV ITEM Id] = [INV Item Location].[INV Item ID] SET [INV Item Location].[Qty Bal] = 0, [INV Item Location].[Qty Avail] = 0, [INV Items].[INV ITEM Qty On Hand] = 0, [INV Items].[INV ITEM First Cost] = 0, [INV Items].[INV ITEM Average Cost] = 0, [INV Items].[INV ITEM Last Cost] = 0

 

[JamesLean]

You can only update one table at a time, so you need to split it into two statements:

UPDATE [INV Items]
SET [INV ITEM Qty On Hand] = 0,
  [INV ITEM First Cost] = 0,
  [INV ITEM Average Cost] = 0,
  [INV ITEM Last Cost] = 0
FROM [INV Items] i JOIN [INV Item Location] l ON i.[INV ITEM Id] = l.[INV Item ID]

UPDATE [INV Item Location]
SET [Qty Bal] = 0,
  [Qty Avail] = 0
FROM [INV Items] i JOIN [INV Item Location] l ON i.[INV ITEM Id] = l.[INV Item ID]

BTW, as a general point, it is usually a bad idea to have spaces in your object names as it forces you to use identifiers and makes for hard-to-read code. --James

 

[sguslan]

create proc updproc

UPDATE [INV Items]
SET [INV Item Location].[Qty Bal] = 0, [INV Item Location].[Qty Avail] = 0, [INV Items].[INV ITEM Qty On Hand] = 0, [INV Items].[INV ITEM First Cost] = 0, [INV Items].[INV ITEM Average Cost] = 0, [INV Items].[INV ITEM Last Cost] = 0

from [INV Items]

INNER JOIN [INV Item Location] ON [INV Items].[INV ITEM Id] = [INV Item Location].[INV Item ID]

go ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[sguslan]

Sorry, i did not pay full attention. JamesLean post is the correct one ! ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[dickiebird]

I would recommend removing spaces from ALL names !
You have to do each table separately :

UPDATE Items
SET Qty On Hand = 0,
First Cost = 0,
Average Cost = 0,
Last Cost = 0
from Items, ItemLocation
where Items.Id = ItemLocation.ItemId

UPDATE ItemLocation
SET Qty Bal = 0,
Qty Avail = 0
from Items, ItemLocation
where Items.Id = ItemLocation.ItemId
Dickie Bird (-)))