Hi,
We use the alias table to store barcodes and found that a recent import of new items had the wrong bar codes attached to them.
I need to run an sql query to delete all of the aliases that match a certain department and SubDescription1.
Very little experience in SQL.
Would anyone be able to point me in the right direction to design the query and send it to each store.
I have a test database that i can use to verify it works.
Thanks
Shane
*This post is locked for comments
Something's wrong with the department name u use, there shouldn't be any problem in using the name, but if it' still not working, try the department code, please note that using ( like 'force ten') is the same as ( = 'force ten'), to be able to benefit from the LIKE operator, u need to consider the '%', for example ( like '%force ten%')
No real luck.
Changed the query a bit so it just lists results rather than deletes for the moment.
Now
USE Rmsdalkey
select ItemID
from Alias
where itemid in
(select item.ID from Item inner join department on item.DepartmentID=DepartmentID where SubDescription1 like '%SS14%'and department.Name like 'force ten')
If i do this it lists over 10000 records IE All that match SS14
If i use department.ID='3383' it lists 5 (Which is correct.)
I think this works because it is pulling the department.ID from the Item table (Same as where it is pulling the SubDescription1
What is your criteria? Do u want to consider the items with a certain subdescription belonging to a certain department? or u want to consider items with a certain subscription OR items in a certain department?
try changing the (and department.name ='zzzz') to ( and department.name like '%zzzz%') or ( and department.code='zzzz')
Ran into a problem.
When i ran it based on the code above it deleted all aliases that corresponded to the subdescription1 criteria but ignored department.name criteria.
I changed the code to use department.id instead and it worked but left me with the problem that the department.id is not necessarly the same in each store. Only have 5 stores so not a major problem but would like to learn why it did not work.
Any ideas.
Shane
Thats great. Works great on the test database.
Much appreciated.
Shane
delete alias
where itemid in
(select item.id from item inner join department on item.departmentid=department.id
where subdescription1 like '%xxx%' and department.name ='zzzz')
A Hassan
RMS Leaders
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,902 Super User 2024 Season 2
Martin Dráb 229,336 Most Valuable Professional
nmaenpaa 101,156