Inventory update problem

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?

UPDATE Inventory.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.Invoice = 207070202)

Thanks

Quote:

Originally Posted by Kliot

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?

UPDATE Inventory.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.Invoice = 207070202)

Thanks

I think it is not possible in SQL Server. But it will work in MS Access.

You have to fetch record and then update it|||hi

i have gone through ur query, but if possible just send me 1 or two records of each table and tell me exactily what u want|||Here is an example,

Invoice table

Code|||Here is an example,

Invoice table

Code Quantity
DM01 2
LG02 2
DM01 3
QP76 1

The update query will update the Inventory table quantity for DM01 by 2 not 5, the second DM01 is not updated

I can get around this by doing a sum query inside the select but it's not ideal.

UPDATE Inventory.Inventory
set RQty = Inventory.Inventory.RQty - od.Quantity
FROM (SELECT Code, SUM(Quantity) AS Quantity FROM Retail.OrderDetails WHERE Invoice = 207022101
GROUP BY Code) as od WHERE(Inventory.inventory.code = od.code)

Invalid Object Name

I restored all Databases in other server.

First I restored the master database and after the others databases. But when I connect by Query Analyser with a user that is a DBO and I execute a select the system return : "Invalid object name 'XXXX'"

My MS-SQL is the version 7.0.What happens when you run this query on that database:

select uid, name
from sysobjects
where name = 'XXXX'

where 'XXXX' is the name of the table you are after.

Invalid object name

Hi,

I have two tables in differents databases : Master database : ServerInformation where there is a table called "Clientes" and Table "Documentos" in the Database Index2003

What I need to do via Trigger is update the table "Documentos" in the field "Cliente" everytime the "Clientes" table change the field 'Cliente'.

Im using the follow Trigger

CREATE TRIGGER UPDate_Documentos_Index2003 ON dbo.Clientes
FOR UPDATE
AS

UPDATE [dbo].[Index2003].[Documentos]
SET [dbo].[Index2003].[Documentos].Cliente = i.Cliente
FROM Inserted i
INNER JOIN [dbo].[Index2003].[Documentos] D
ON D.ID_Clientes = i.ID_Clientes

When I commit the change in the register "Clientes" arise the follow message :

Invalid object name 'dbo.Index2003.Documentos'

Have I doing something wrong ?

Thanks for attetion

Leonardo AlmeidaOriginally posted by vectords
Hi,

I have two tables in differents databases : Master database : ServerInformation where there is a table called "Clientes" and Table "Documentos" in the Database Index2003

What I need to do via Trigger is update the table "Documentos" in the field "Cliente" everytime the "Clientes" table change the field 'Cliente'.

Im using the follow Trigger

CREATE TRIGGER UPDate_Documentos_Index2003 ON dbo.Clientes
FOR UPDATE
AS

UPDATE [dbo].[Index2003].[Documentos]
SET [dbo].[Index2003].[Documentos].Cliente = i.Cliente
FROM Inserted i
INNER JOIN [dbo].[Index2003].[Documentos] D
ON D.ID_Clientes = i.ID_Clientes

When I commit the change in the register "Clientes" arise the follow message :

Invalid object name 'dbo.Index2003.Documentos'

Have I doing something wrong ?

Thanks for attetion

Leonardo Almeida

Nice try ;)

If you did not linked server - do it.

BOL: Use Accessing Linked Servers
After a linked server is created using sp_addlinkedserver, it can be accessed using:
Distributed queries. Accessing tables in the linked server through SELECT, INSERT, UPDATE, and DELETE statements using a linked server-based name (server.database.dbowner.object).|||More simply, you just qualified your table incorrectly. It should be

[Index2003].[dbo].[Documentos]

Invalid object name

Hi,

I have two tables in differents databases : Master database :
ServerInformation where there is a table called "Clientes" and Table
"Documentos" in the Database Index2003

What I need to do via Trigger is update the table "Documentos" in the
field "Cliente" everytime the "Clientes" table change the field
'Cliente'.

Im using the follow Trigger

CREATE TRIGGER UPDate_Documentos_Index2003 ON dbo.Clientes
FOR UPDATE
AS

UPDATE [dbo].[Index2003].[Documentos]
SET [dbo].[Index2003].[Documentos].Cliente = i.Cliente
FROM Inserted i
INNER JOIN [dbo].[Index2003].[Documentos] D
ON D.ID_Clientes = i.ID_Clientes

When I commit the change in the register "Clientes" arise the follow
message :

Invalid object name 'dbo.Index2003.Documentos'

Have I doing something wrong ?

Thanks for attetion

Leonardo Almeida

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Try this (untested):

CREATE TRIGGER UPDate_Documentos_Index2003 ON dbo.Clientes FOR UPDATE
AS

UPDATE [Index2003].[dbo].[Documentos]
SET Cliente =
(SELECT Cliente
FROM inserted
WHERE ID_Clientes =
[Index2003].[dbo].[Documentos].ID_Clientes)
WHERE ID_Clientes
IN (SELECT ID_Clientes FROM inserted)

--
David Portas
----
Please reply only to the newsgroup
--|||Leonardo Almeida (leonardoalmeida2004@.yahoo.com.br) writes:
> CREATE TRIGGER UPDate_Documentos_Index2003 ON dbo.Clientes
> FOR UPDATE
> AS
> UPDATE [dbo].[Index2003].[Documentos]
> SET [dbo].[Index2003].[Documentos].Cliente = i.Cliente
> FROM Inserted i
> INNER JOIN [dbo].[Index2003].[Documentos] D
> ON D.ID_Clientes = i.ID_Clientes
>
> When I commit the change in the register "Clientes" arise the follow
> message :
> Invalid object name 'dbo.Index2003.Documentos'

You have swapped database name and ownername. Use Index2003..Documentos
instead.

Also, in the left-hand side of the SET-clause, you should use any
prefix at all.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp