Post reply

Need to reformat a column and combine two rows

  • August 14, 2018 at 11:36 pm

    #361045

    I have a large amount of data that I input into a table in SQL. 2741 rows to be exact. On of my columns (Lot) failed to go in with the correct leading spaces. The datatype is nvarchar(15).  I am trying to use this statement to correct the leading spaces:
    Update lot_loc_mst
    set lot = substring('     ',1,15 - len(ltrim(lot_loc_mst.lot))) + ltrim(lot_loc_mst.lot)

    However, since the Lot records did not have the leading spaces, when the customer moved forward with their process, the ERP created the record with the correct lot format,  but now the 'qty_on_hand' is incorrect. 

    I need to be able to update the format of the lot column, using the statement above, while at the same time, combining the rows that would become duplicates. You can see an example in the picture below. 
    Select lot, * from lot_loc_mst where item = '01068'
     
    Row 1 & 2 have the correct "Lot" format. When I run the above 'Update' statement, I get a foreign key error because, once formatted, I will have duplicate rows (1 & 6, 2 & 7). The key is 'whse, item, loc, lot'. I need these rows to combine with the qty_on_hand for 1&6 =336 and 2&7 =48.

    I have several rows to combine, so a general script, that I can edit, would be extremely helpful....if at all possible...

    Any help?

  • August 15, 2018 at 12:23 am

    #2001780

    Please post the DDL (create table) script, sample data as an insert statement and the desired result.
    😎

  • August 15, 2018 at 2:14 am

    #2001788

    First add qty_on_hand for rows where lot will change and the new lot already exists, eg


    UPDATE b
    SET  b.qty_on_hand += a.qty_on_hand
    FROM lot_loc_mst a
     JOIN lot_loc_mst b
      ON b.whse = a.whse AND b.item = a.item AND b.loc = a.loc AND b.lot = RIGHT(SPACE(15)+a.lot,15)
    WHERE DATALENGTH(a.lot) < 15;

    Next delete lots that were added from


    DELETE a
    FROM lot_loc_mst a
     JOIN lot_loc_mst b
      ON b.whse = a.whse AND b.item = a.item AND b.loc = a.loc AND b.lot = RIGHT(SPACE(15)+a.lot,15)
    WHERE DATALENGTH(a.lot) < 15;

    Next update remaining lots


    UPDATE lot_loc_mst
    SET  lot = RIGHT(SPACE(15)+lot,15)
    WHERE DATALENGTH(lot) < 15;

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 3 posts - 1 through 2 (of 2 total)

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