Post reply

Cleaning up SKU's in my master POS DB, need to move SKU's with check digit to temp table

  • March 8, 2017 at 3:26 pm

    #335610

    So when I run this simple query:
    select * from Inventory
    where Dept_ID = '1123'

    Department ID 1123 is Cigarette Cartons.
    We program our Honeywell scanners to DROP all the 'check digits' (the very last digit in a UPC/SKU)

    So for example, we have the same brand of Cigarettes, EVE 120 LIGHTS
    1100012808
    11000128083

    You can see the redundancy here. I need to eliminate ALL (697 rows returned from the query above) rows that have the 'check digit' so doing the normal:
    UPDATE INVENTORY
    SET ItemNum = '1100012808'
    WHERE Dept_ID = '1123' and ' 11000128083'

    This wont work as I would have to do this over and over for each item, I am looking for the dynamic way...

    Now, you may say, well that is easy, just DROP all ItemNum with 11 digits right? Well, no, because there are UPC's that have the 'short' version as well...UPC-A (long SKU) and UPC-E0 (short SKU) and the short sku is only 6 digits.

    The main thing I want to do is not delete the rows, but move them to a temporary table and that part, i don't know how to do.

    As always, I love the help I get here and I am always grateful for it.

    Thanks again!

  • March 8, 2017 at 4:45 pm

    #1932432

    chef423 - Wednesday, March 8, 2017 3:26 PM

    So when I run this simple query:
    select * from Inventory
    where Dept_ID = '1123'

    Department ID 1123 is Cigarette Cartons.
    We program our Honeywell scanners to DROP all the 'check digits' (the very last digit in a UPC/SKU)

    So for example, we have the same brand of Cigarettes, EVE 120 LIGHTS
    1100012808
    11000128083

    You can see the redundancy here. I need to eliminate ALL (697 rows returned from the query above) rows that have the 'check digit' so doing the normal:
    UPDATE INVENTORY
    SET ItemNum = '1100012808'
    WHERE Dept_ID = '1123' and ' 11000128083'

    This wont work as I would have to do this over and over for each item, I am looking for the dynamic way...

    Now, you may say, well that is easy, just DROP all ItemNum with 11 digits right? Well, no, because there are UPC's that have the 'short' version as well...UPC-A (long SKU) and UPC-E0 (short SKU) and the short sku is only 6 digits.

    The main thing I want to do is not delete the rows, but move them to a temporary table and that part, i don't know how to do.

    As always, I love the help I get here and I am always grateful for it.

    Thanks again!

    So the length of long UPC with check digit is 11, and the length for short UPC with check digit is 7, right?

    INSERT..
    SELECT..
    FROM ..
    WHERE LEN(UPC) IN (7, 11)

    DELETE..FROM ..
    WHERE LEN(UPC) IN (7, 11)

    You can get fancy and do a DELETE and OUTPUT the DELETEd rows in to the holding table if you like.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • March 9, 2017 at 9:23 am

    #1932534

    TheSQLGuru - Wednesday, March 8, 2017 4:45 PM

    chef423 - Wednesday, March 8, 2017 3:26 PM

    So when I run this simple query:
    select * from Inventory
    where Dept_ID = '1123'

    Department ID 1123 is Cigarette Cartons.
    We program our Honeywell scanners to DROP all the 'check digits' (the very last digit in a UPC/SKU)

    So for example, we have the same brand of Cigarettes, EVE 120 LIGHTS
    1100012808
    11000128083

    You can see the redundancy here. I need to eliminate ALL (697 rows returned from the query above) rows that have the 'check digit' so doing the normal:
    UPDATE INVENTORY
    SET ItemNum = '1100012808'
    WHERE Dept_ID = '1123' and ' 11000128083'

    This wont work as I would have to do this over and over for each item, I am looking for the dynamic way...

    Now, you may say, well that is easy, just DROP all ItemNum with 11 digits right? Well, no, because there are UPC's that have the 'short' version as well...UPC-A (long SKU) and UPC-E0 (short SKU) and the short sku is only 6 digits.

    The main thing I want to do is not delete the rows, but move them to a temporary table and that part, i don't know how to do.

    As always, I love the help I get here and I am always grateful for it.

    Thanks again!

    So the length of long UPC with check digit is 11, and the length for short UPC with check digit is 7, right?

    INSERT..
    SELECT..
    FROM ..
    WHERE LEN(UPC) IN (7, 11)

    DELETE..FROM ..
    WHERE LEN(UPC) IN (7, 11)

    You can get fancy and do a DELETE and OUTPUT the DELETEd rows in to the holding table if you like.

    Thank you, but I am still unsure how to move the SKU's with an 11 digit length to a temp table.

  • March 9, 2017 at 12:16 pm

    #1932583

    Thank you, but I am still unsure how to move the SKU's with an 11 digit length to a temp table.

    Did you set up a quick demo and see what my code did with length 11 rows??

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • March 9, 2017 at 4:07 pm

    #1932625

    TheSQLGuru - Thursday, March 9, 2017 12:16 PM

    Thank you, but I am still unsure how to move the SKU's with an 11 digit length to a temp table.

    Did you set up a quick demo and see what my code did with length 11 rows??

    Yes, but I am getting this error...

    The DELETE statement conflicted with the REFERENCE constraint "fkInventory_AdditionalInfoInventory". The conflict occurred in database "sanroquethurs", table "dbo.Inventory_AdditionalInfo".
    The statement has been terminated.

    The Inventory_AdditionalInfo table is very basic, only has 5-6 binary (1 or 0) fields, the only two fields that reference each other, from the Inventory & Inventory_AdditionalInfo tables is the ItemNum field.

    So I need to update my query to satisfy this, isn't this query just a simple INNER JOIN?

    DELETE FROM Inventory
    WHERE Dept_ID = '1123' and LEN(ItemNum) = 10

    So it may look like:
    DELETE FROM Inventory, Inventory_additionalInfo
    INNER JOIN ItemNum.Inventory = ItemNum.Inventory_AdditionalInfo
    WHERE Dept_ID = '1123' and LEN(ItemNum.Inventory) = 10

    But that doesnt work, I'm not good with multiple table deletes.

  • March 9, 2017 at 9:03 pm

    #1932640

    Ok, so I have read over n over you cannot delete related data from 2 tables in one query. I guess a FK is the way to go, never written one. Id give a buffalo nickle for a quick lesson with my table headers.

    Web is not good, I need 'hands on' 🙂

    Thanks folks.

    Chris

  • March 10, 2017 at 3:33 pm

    #1932805

    chef423 - Thursday, March 9, 2017 9:03 PM

    Ok, so I have read over n over you cannot delete related data from 2 tables in one query. I guess a FK is the way to go, never written one. Id give a buffalo nickle for a quick lesson with my table headers.

    Web is not good, I need 'hands on' 🙂

    Thanks folks.

    Chris

    FKs with Cascaded Deletes are a scary thing in my book. I would much rather handle this type of thing explicitly. YMMV

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply