Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    Unanswered: oracle query required

    hi please help me in converting this sql server into oracle

    UPDATE t1
    SET t1.H = 'I'
    FROM dbo.ABC AS t1
    INNER JOIN (
    SELECT DISTINCT MIN(A) AS A
    FROM dbo.ABC
    GROUP BY C
    HAVING COUNT(*) > 1
    ) AS x ON x.A = t1.A

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You might get (more) responses if you provided DDL for tables involved,
    DML (INSERT) to populate test data in tables & actual results from SQL against the sample data.
    Since I am not a SQL Server person & am not sure EXACTLY what the post SQL does.

    It would give us a fighting chance to reproduce what exists now.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Try this:
    Code:
    UPDATE abc
    SET h = 'I' 
    WHERE a IN (SELECT MIN(a) 
                FROM abc 
                GROUP BY C 
                HAVING COUNT(*) > 1)

  4. #4
    Join Date
    Mar 2010
    Posts
    9
    Table: ABC
    A B C D E F G H
    50586 152476 Dextromethorphan-Guaifenesin 10-100 mg/5 mL Tussin DM 10 mg-100 mg/5 mL Syrup R
    89446 433219 Dextromethorphan-Guaifenesin 10-100 mg/5 mL Tussin DM 10 mg-100 mg/5 mL Syrup A
    165511 433219 Dextromethorphan-Guaifenesin 10-100 mg/5 mL Tussin DM 10 mg-100 mg/5 mL Syrup A

    i want to update Table ABC COLUMN H as 'I' If all columns matches except A

    WHAT IS THE UPDATE QUERY IN DOING SO.

    SOMEWHERE TRIED LIKE

    UPDATE TABLE ABC SET H='I' WHERE A IN (SELECT MIN(A) FROM ABC GROUP BY C HAVING COUNT(*)>1)

    I AM HAVING MORE TAHN 1.5 LAKH RECORDS IN MY TABLE.HOW TO UPDATE IF IT MATCHES ABOVE CRITERIA.....

  5. #5
    Join Date
    Jul 2006
    Location
    Bangalore
    Posts
    57
    I would have prefered follwing way to update 1.5 LAKH record
    1) Create empty tmp table as source table.
    2) Load updated data into tmp table using nolog option.
    3) Clean source table.
    4) Load updated data from tmp table to source table using nolog option.
    Ritesh Kumar Singh
    IBM Certified DB2 DBA for LUW
    **Knowledge Is Theft If Not Shared !!**

  6. #6
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Still not sure what you're attempting to do, But try this sql out, it should update all rows where columns(b thru h) are the same on more than one row:

    Code:
    update abc
      set  h  =  'I'
    where  ( b, c, d, e, f, g, h ) in
           ( select b, c, d, e, f, g, h, count(*)
              from  abc
             group by b, c, d, e, f, g, h
             having count(*) > 1 )

  7. #7
    Join Date
    Mar 2010
    Posts
    9
    Thank you very much

Posting Permissions

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