Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Update query help

    I have two tables, TabA and TabB. TabA contains records that are basically the master template for TabB records.
    If anyone changes any rows in TabB to anything other than the values listed in the template, we will update the records daily in TabB to
    what they should be.


    TabA:

    Col1 Col2 Col3 Col4 Col5 Col6 | Col7 Col8 Col9 Col10 Col11 Col2
    -----------------------------------------------------------------------|-----------------------------------------------------------------------------
    S * * * P*N * | 1 123D 1 7D 38D 12

    Columns 1-6 are the criteria, and 7-12 are what they should be set to. In this example, if there are records in TabB
    with an 'S' in Col1, anything (wildcard) in Col2, anything (wildcard) in Col3, anything (wildcard) in Col4, anything
    3 characters wide, beginning with 'P'and ending with 'N' in Col5, anything in Col6, set them equal to the values in
    columns 7-12.


    TabB Before:

    Col1 Col2 Col3 Col4 Col5 Col6 | Col7 Col8 Col9 Col10 Col11 Col2
    -----------------------------------------------------------------------|------------------------------------------------------------------------------
    S 301 H473 SLP PAN ROC | 12 16D 10 7D 41D 19


    TabB Becomes:

    Col1 Col2 Col3 Col4 Col5 Col6 | Col7 Col8 Col9 Col10 Col11 Col2
    ---------------------------------------------------------------------- |-----------------------------------------------------------------------------
    S 301 H473 SLP PAN ROC | 1 123D 1 7D 38D 12


    Is there a way to accomplish this using SQL?

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Maybe this could work, but I don't know if MERGE will
    accept decode in it

    MERGE INTO TabB B
    USING TabA A
    ON (B.Col1 = decode(A.Col1, '*', B.Col1, A.COl1)
    AND B.Col2 = decode(A.Col2, '*', B.Col2, A.COl2)
    AND B.Col3 = decode(A.Col3, '*', B.Col3, A.COl3)
    AND B.Col4 = decode(A.Col4, '*', B.Col4, A.COl4)
    AND B.Col5 = decode(A.Col5, '*', B.Col5, A.COl5)
    AND B.Col6 = decode(A.Col6, '*', B.Col6, A.COl6) )
    WHEN MATCHED
    THEN
    UPDATE SET
    B.Col7 = A.Col7,
    B.Col8 = A.Col8,
    B.Col9 = A.Col9,
    B.Col10 = A.Col10,
    B.Col11 = A.Col11,
    B.Col12 = A.Col12
    WHEN NOT MATCHED
    THEN
    INSERT
    (B.Col1,
    B.Col2,
    B.Col3,
    B.Col4,
    B.Col5,
    B.Col6,
    B.Col7,
    B.Col8,
    B.Col9,
    B.Col10,
    B.Col11,
    B.Col12)
    VALUES
    ('X','X','X','X','X','X','X','X','X','X','X','X'
    );

    now just delete inserted row you don't need

    delete
    from TabB
    where Col1 = 'X'
    and Col2 = 'X'
    .
    .
    .;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •