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.Inven tory
set RQty = Inventory.Inven tory.RQty...
Kliot
Last Activity: Dec 6 '07, 08:04 PM
Joined: Sep 15 '06
Location: Home
-
Here is an example,
Invoice table
CodeLeave a comment:
-
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.Inven tory
SET Qty = Inventory.Inven tory.Qty... -
Actually I was shown an easy that it can be easily done with a
Insert, Select Query
insert into my_table (field1, field2, field3)
select field 1,
field 2,
case
when field3 = 1
then field3 * 10 / 200
when field3 = 4
then field3 * 20 / 50
end
from my_table
where field1 = 5Leave a comment:
-
Select and Insert Question
I have a table that I want to query and modify the data in some columns then insert the result back into the same table as new records. The index column will be changed so there will not be a worry about an index violation.
I have no problem running the select query to modify the records, but I'm not sure what's the best way to insert the result back into the table. I'm hoping that there is a way I can do this in one query
...
No activity results to display
Leave a comment: