constructUpdateSQL method

Implementation

String constructUpdateSQL(TCustomSQLQuery query, TBoolRef returningClause) {
  var sqlSet = '';
  final sqlWhere = TStringRef("");
  final usedEmptyKey = TBoolRef(false); // @@@ whether the empty-key tolerance condition was used
  var returningFields = '';
  for (var x = 0; x < query.fields.count; x++) {
    final f = query.fields[x];
    // @@@ lookup/calc columns are not physical fields, and must never go into an UPDATE (even if providerFlags
    //     mistakenly carries pfInUpdate). The WHERE part is skipped too.
    if (f.fieldKind == TFieldKind.fkCalculated ||
        f.fieldKind == TFieldKind.fkLookup) {
      continue;
    }
    addFieldToUpdateWherePart(sqlWhere, query.updateMode, f, usedEmptyKey);
    if (f.providerFlags.contains(TProviderFlag.pfInUpdate) && !f.readOnly) {
      sqlSet =
          '$sqlSet${fieldNameQuoteChars[0]}${f.fieldName}${fieldNameQuoteChars[1]}=:"${f.fieldName}",';
    }
    if (returningClause.value &&
        f.providerFlags.contains(TProviderFlag.pfRefreshOnUpdate)) {
      returningFields =
          '$returningFields${fieldNameQuoteChars[0]}${f.fieldName}${fieldNameQuoteChars[1]},';
    }
  }
  if (sqlSet.isEmpty) databaseErrorFmt(SNoUpdateFields, ['update'], this);
  sqlSet = sqlSet.substring(0, sqlSet.length - 1);
  if (sqlWhere.value.isEmpty) {
    sqlWhere.value = _fallbackKeyWhere(query, usedEmptyKey); // @@@ no key → use the first field's old value as the condition
  }
  if (sqlWhere.value.isEmpty) {
    databaseErrorFmt(SNoWhereFields, ['update'], this);
  }
  final _updTbl = query.updateTableName.isNotEmpty ? query.updateTableName : query._tableName;
  var result =
      'update $_updTbl set $sqlSet where ${sqlWhere.value}';
// aa ??? issue
  // The empty-key tolerance condition (cno is null or cno = '') can match more than one row — in legacy data,
  // an empty code often has several matching rows. Adds limit 1 to ensure only one row is changed at a time, not the whole batch.
  // Only added when the tolerance condition is actually used; a normal UPDATE with a real primary key is completely unaffected.
  if (usedEmptyKey.value) result = '$result limit 1';
// zz ??? issue
  if (returningClause.value) {
    returningClause.value = returningFields.isNotEmpty;
    if (returningClause.value) {
      returningFields =
          returningFields.substring(0, returningFields.length - 1);
      result = '$result returning $returningFields';
    }
  }
  return result;
}