Compare two and write updated output in third file

Shell Programming and Scripting
1

Hi Linux Experts.

I have a requirement where i need to update the thousands of table definitions to extend the column length and character set therefore i am looking for some sort of linux script which i can use to update the length and chacterset.

I have two files

In first file i have 7 below columns.

DataBaseName,TableName,ColumnName,ColumnLength,ColumnCharacterSet,Ext_ColumnLength,Changed_ColumnCharacterSet

where as in Second file i have ddls of all the tables.

Now first i want to compare the Databasename.TableName from first file with the DatabaseName.TableName in second file if there is a match in second file then update the column parameters i.e., 'ColumnName ColumnLength ColumnCharacterSet' with 'ColumnName Ext_ColumnLength Changed_ColumnCharacterSet'

Sample files and required outout is given below.

DB_TEST|TABLE_TEST|DEF|100|Latin|270|Unicode
DB_TEST|TABLE_TEST|JKL|100|Latin|270|Unicode

File2

    CREATE MULTISET TABLE DB_TEST.TABLE_TEST ,NO FALLBACK ,
         NO BEFORE JOURNAL,``
         NO AFTER JOURNAL,
         CHECKSUM = DEFAULT,
         DEFAULT MERGEBLOCKRATIO
         (
          ABC VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,
          DEF VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
          GHI VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
          JKL VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
          )
    INDEX ( ABC ) ;

Required OutPut

    CREATE MULTISET TABLE DB_TEST.TABLE_TEST ,NO FALLBACK ,
         NO BEFORE JOURNAL,``
         NO AFTER JOURNAL,
         CHECKSUM = DEFAULT,
         DEFAULT MERGEBLOCKRATIO
         (
          ABC VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,
          DEF VARCHAR(240) CHARACTER SET UNICODE NOT CASESPECIFIC,
          GHI VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
          JKL VARCHAR(240) CHARACTER SET UNICODE NOT CASESPECIFIC,
          )
    INDEX ( ABC ) ;

Thanks for your help.

2

Hi, try:

awk '
  NR==FNR{
    $0=toupper($0)
      A[$1"."$2,$3,$4]=$6
      A[$1"."$2,$3,$5]=$7
      next
    }
    $1=="CREATE"{
      t=$4
    }
    $2~/VARCHAR/ {
      split($2,F,/[()]/)
      if ((t,$1,F[2]) in A)
        sub(F[2],A[t,$1,F[2]])
      if ((t,$1,$5) in A)
        sub($5, A[t,$1,$5])
  }
  1
' FS=\| file1 FS=" " file2