def updateTable(self, table, cols=None, data=None, commit=False):
        # generate SQL
        # note: comparison by = will never match NULL fields
        # use 'IS NOT DISTINCT FROM' to match nulls, but this is not indexed and verrrrry slooooow
        if not cols or not data:
            # nothing can be done, just return
            return () # place breakpoint here to debug
        isOracle = self.product == "orcl"
        isSQLite = self.product == "sqlite"
        idCol = cols[0]
        colTypeFunctions = self.columnTypeFunctions(table)
        colDeclarations = self.tableColDeclaration.get(table)
        try:
            colTypeCast = tuple(colTypeFunctions[colName][0] for colName in cols)
        except KeyError as err:
            raise XPDBException("xpgDB:MissingColumnDefinition",
                                _("Table %(table)s column definition missing: %(missingColumnName)s"),
                                table=table, missingColumnName=str(err)) 
        rowValues = []
        for row in data:
            colValues = []
            for col in row:
                if isinstance(col, bool):
                    colValues.append('TRUE' if col else 'FALSE')
                elif isinstance(col, (int,float)):
                    colValues.append(str(col))
                elif col is None:
                    colValues.append('NULL')
                else:
                    colValues.append(self.dbStr(col))
            if not rowValues and self.product == "postgres":  # first row
                for i, cast in enumerate(colTypeCast):
                    if cast:
                        colValues[i] = colValues[i] + cast
            rowColValues = ", ".join(colValues)
            if isOracle:
                rowValues.append(rowColValues)
            elif isSQLite:
                rowValues.append(colValues)
            else:
                rowValues.append("(" + rowColValues + ")")
        if not isOracle and not isSQLite:
            values = ", \n".join(rowValues)
        
        _table = self.dbTableName(table)
        _inputTableName = self.tempInputTableName
        if self.product == "postgres":
            # insert new rows, return id and cols of new and existing rows
            # use IS NOT DISTINCT FROM instead of = to compare NULL usefully
            sql = [('''
WITH input (%(valCols)s) AS ( VALUES %(values)s ) 
   UPDATE %(table)s t SET %(settings)s 
   FROM input i WHERE i.%(idCol)s = t.%(idCol)s
;''') %         {"table": _table,
                 "idCol": idCol,
                 "valCols": ', '.join(col for col in cols),
                 "settings": ', '.join('{0} = i.{0}'.format(cols[i])
                                       for i, col in enumerate(cols)
                                       if i > 0),
                 "values": values}]
                 
        elif self.product == "mysql":
            sql = ["CREATE TEMPORARY TABLE %(inputTable)s ( %(valCols)s );" %
                        {"inputTable": _inputTableName,
                         "valCols": ', '.join('{0} {1}'.format(col, colDeclarations[col])
                                              for col in cols)},
                   "INSERT INTO %(inputTable)s ( %(newCols)s ) VALUES %(values)s;" %     
                        {"inputTable": _inputTableName,
                         "newCols": ', '.join(cols),
                         "values": values},
                   "LOCK TABLES %(inputTable)s AS i READ, %(table)s AS t WRITE;" %     
                        {"inputTable": _inputTableName,
                         "table": _table},
                   "UPDATE %(inputTable)s i, %(table)s t SET %(settings)s WHERE i.%(idCol)s = t.%(idCol)s;" %     
                        {"inputTable": _inputTableName,
                         "table": _table,
                         "idCol": idCol,
                         "settings": ', '.join('t.{0} = i.{0}'.format(cols[i])
                                               for i, col in enumerate(cols)
                                               if i > 0)},
                   "DROP TEMPORARY TABLE %(inputTable)s;" % {"inputTable": _inputTableName}]
        elif self.product == "mssql":
            sql = ["CREATE TABLE #%(inputTable)s ( %(valCols)s );" %
                        {"inputTable": _inputTableName,
                         "valCols": ', '.join('{0} {1}'.format(col, colDeclarations[col])
                                              for col in cols)}]
            # must break values insertion into 1000's each
            for i in range(0, len(rowValues), 950):
                values = ", \n".join(rowValues[i: i+950])
                sql.append("INSERT INTO #%(inputTable)s ( %(cols)s ) VALUES %(values)s;" %     
                        {"inputTable": _inputTableName,
                         "cols": ', '.join(cols),
                         "values": values})
            sql.append("MERGE INTO %(table)s USING #%(inputTable)s ON (#%(inputTable)s.%(idCol)s = %(table)s.%(idCol)s) "
                       "WHEN MATCHED THEN UPDATE SET %(settings)s;" %
                        {"inputTable": _inputTableName,
                         "table": _table,
                         "idCol": idCol,
                         "settings": ', '.join('{0}.{2} = #{1}.{2}'.format(_table, _inputTableName, cols[i])
                                               for i, col in enumerate(cols)
                                               if i > 0)})
            sql.append("DROP TABLE #%(inputTable)s;" % {"inputTable": _inputTableName})
        elif self.product == "orcl":
            sql = ["CREATE GLOBAL TEMPORARY TABLE %(inputTable)s ( %(valCols)s )" %
                        {"inputTable": _inputTableName,
                         "valCols": ', '.join('{0} {1}'.format(col, colDeclarations[col])
                                              for col in cols)}]
            for i in range(0, len(rowValues), 500):
                sql.append(
                   "INSERT INTO %(inputTable)s ( %(cols)s ) %(values)s" %     
                        {"inputTable": _inputTableName,
                         "cols": ', '.join(cols),
                         "values": "\nUNION ALL".join(" SELECT {} FROM dual ".format(r)
                                                      for r in rowValues[i:i+500])})
            sql.append("MERGE INTO %(table)s USING %(inputTable)s ON (%(inputTable)s.%(idCol)s = %(table)s.%(idCol)s) "
                       "WHEN MATCHED THEN UPDATE SET %(settings)s" %
                        {"inputTable": _inputTableName,
                         "table": _table,
                         "idCol": idCol,
                         "settings": ', '.join('{0}.{2} = {1}.{2}'.format(_table, _inputTableName, cols[i])
                                               for i, col in enumerate(cols)
                                               if i > 0)})
            sql.append("DROP TABLE %(inputTable)s" % {"inputTable": _inputTableName})
        elif self.product == "sqlite":
            sql = ["UPDATE %(table)s SET %(settings)s WHERE %(idCol)s = %(idVal)s;" %     
                            {"table": _table,
                             "idCol": idCol,
                             "idVal": rowValue[0],
                             "settings": ', '.join('{0} = {1}'.format(col,rowValue[i])
                                                   for i, col in enumerate(cols)
                                                   if i > 0)}
                   for rowValue in rowValues]
        if TRACESQLFILE:
            with io.open(TRACESQLFILE, "a", encoding='utf-8') as fh:
                fh.write("\n\n>>> accession {0} table {1} sql length {2} row count {3}\n"
                         .format(self.accessionId, table, len(sql), len(data)))
                for sqlStmt in sql:
                    fh.write(sqlStmt)
        for sqlStmt in sql:
            self.execute(sqlStmt,commit=commit, fetch=False, close=False)
dataset/ETHPy150Open Arelle/Arelle/arelle/plugin/xbrlDB/SqlDb.py/SqlDbConnection.updateTable/original