Last post Oct 02, 2019 03:54 PM by knnth192o
Sep 21, 2019 08:37 PM|knnth192o|LINK
I need to update/insert multiple items' locations (shelf numbers).
It can be 10 items or sometimes 50, 100... The number of rows will change every time, so a simple insert query won't work.
Is there an efficient way to achieve this?
Here's what I'm trying to do in detail:
ItemA - 100A
ItemC - 104B
ItemK - 106C
ItemT - 110A
ItemZ - 120D
Thank you so much for your help!
Sep 23, 2019 08:36 AM|PatriceSc|LINK
Depends on your db. If SQL Server you could try
https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.8 or maybe https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-2017 but
if around 100 rows, would just start with a simple INSERT as it's unlikely you'll need a huge an optimization here.
I would insert data into an import table so that I can further check whatever I want and then would use https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017 to
sync the destination table.
Sep 23, 2019 09:38 AM|samwu|LINK
Receiving a new shipment in the warehouse, 5 items total, alphabetical order (item numbers from a purchase order)
e.g. ItemA, ItemC, ItemK, ItemT, ItemZ Retrieving available shelf numbers from database, 5 shelf numbers (a query that selects available shelf numbers)
e.g. 100A, 104B, 106C, 110A, 120D These 5 shelf numbers (alphabetical order) need to be inserted (or updated) into each item's location column.
What database do you use？
In general, we use "select column1 from [table] order by column1 asc" to to sort the data.
Then use "select column1+column2 as column3 from [table]" to merge them.
Sep 30, 2019 07:58 PM|knnth192o|LINK
Thanks a lot for your help. I use MSSQL 2016.
I'm not really a programmer, and it looks like this is too much for me, haha.
I got 5 items from tbl_item:
These items have shelf numbers already.
I got 5 new shelf numbers from tbl_shelf:
How do I update the existing/old shelf numbers to the new ones above?
Is it possible to update the record with a simple Update query?
The shelf table doesn't have any column that's related to the item table.
Thanks so much!!
Sep 30, 2019 08:00 PM|knnth192o|LINK
Thanks a lot, but I don't think that's what I want. Please see above reply to PatriceSc
Oct 01, 2019 10:10 AM|samwu|LINK
Do you mean update the shelf numbers in tbl_item? and the value come from tbl_shelf?
If so, you can try below code:
update tbl_item SET tbl_item.[shelf numbers ] = [tbl_shelf].shelfnumbers from [tbl_shelf] where tbl_item.Id=[tbl_shelf].Id
Oct 01, 2019 07:54 PM|knnth192o|LINK
Thanks so much, but the problem is that the shelf table doesn't have the id column.
Let's just say I have values from a select query result, not from a table:
I need to insert above values to the item table, to these items below, one by one thru an update query:
Is it possible? Thanks so much again!
* I got the location numbers above from a query like this:
SELECT TOP 10 r.RackNo AS 'Location'
FROM tbl_Item i
JOIN tbl_RackSize r ON r.RackNo = i.place
WHERE r.RackNo BETWEEN '201%' AND '220%'
GROUP BY r.RackNo, r.RackSize
HAVING (CAST(r.RackSize AS int) - SUM(i.qty)) > 15
ORDER BY 'Location'
Oct 02, 2019 10:35 AM|samwu|LINK
Let's just say I have values from a select query result, not from a table
one by one thru an update query
This is impossible. it can't be sure that the select query result corresponds to a row value in tbl_item.
Oct 02, 2019 10:44 AM|PatriceSc|LINK
For now a solution I see would be join on a https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017 value
so that item number n (not sure how you know which one should be moved or they are not yet assigned at all ?) could be assigned to the available shelf number n (you have a single item per location ?).
Edit: or a https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017 could
perhaps also help if each item should fit the remainining capacity of a shelve or something like that.
IMO do first something simple that works and see if it really needs to be improved.
Oct 02, 2019 03:54 PM|knnth192o|LINK
Hi PatriceSc & Sam :)
Thanks so much again. I'll go over the links today for sure.
Our back-end system has an uploading function via Excel, so my warehouse guys have been using that. I'm just trying to make a simple program so that they don't have to run queries and upload spreadsheets to update locations. Anyways...
Our purchase order table has columns like these. This list is sorted by ItemName. Each item has its own RackNo (JOIN with item table).
From a select query I made (above), the query finds available locations (simple math) so that my guys don't need to look for empty spaces. I get these for example:
Now, the old locations need to be replaced with the new ones above. That's what I'm trying to achieve. Using ADO.NET preferably. That's the only thing I know, haha.
Like you suggested, I think I should insert data into an import table first, but still, the issue is that the "number of items" will change
all the time, and I don't know how to achieve this. Maybe it's impossible like Sam said?
Thank you so much guys again!