5 Replies Latest reply: Jan 26, 2012 5:40 AM by Miguel Angel Baeyens de Arce RSS

    Remove rows from table that already identified on other table

      Hi,

       

      I have a QVD file with data identified on [report_id, date_of_report]. Let us call this QVD_TABLE.

       

      I now retrieve new records from the database. Let us call this DB_TABLE.

       

      QVD_TABLE and DB_TABLE have the same structure.

       

      I want to add  to QVD_TABLE new rows coming from DB_TABLE but at the same time carry out a refresh, as follows:

      I want to end with QVD_TABLE holding the rows that it had initially, plus all the new rows from DB_TABLE, but for those rows in DB_TABLE that they have the same [report_id, date_of_report] to keep the DB_TABLE rows and remove the QVD_TABLE rows.

       

      Example:

      DB_TABLE:
      SQL SELECT
            id,
           report_id,
           date_of_report,
           value1,
           value2
      FROM report_results;
      
      QVD_TABLE:
      LOAD
          id,
          report_id,
          date_of_report,
          value1,
          value2
      FROM $(vQvdFile) (qvd);
      

       

      Example Data:

       

      DB_TABLE:

      id
      report_id
      date_of_report
      value1
      value2
      1532012-01-01hello
      1632012-01-01
      there
      1742012-01-02johnruby

      QVD_TABLE:

      idreport_iddate_of_report
      value1
      value2
      132012-01-01ciao
      232012-01-01
      faraway
      342011-12-31snoopy

       

      RESULT QVD TABLE has to be:

      idreport_id
      date_of_report
      value1
      value2
      1532012-01-01hello
      1632012-01-01
      there
      342011-12-31snoopy
      1742012-01-02johnruby

       

      (I have put in red the rows of QVD TABLE that have been deleted and replaced by the blue rows of DATA_TABLE)

       

      Now, how can I refresh QVD_TABLE as I described above?

       

      Thanks in advance

      Panayotis