private void EndUpdateSql() { //https://www.orafaq.com/node/2450 if (ConnectionType == ConnectionManagerType.Oracle) { QueryText.AppendLine(") vt "); QueryText.Append(" ON ( "); QueryText.AppendLine(string.Join(Environment.NewLine + " AND ", UpdateJoinColumnNames.Select(col => $@"( ( ut.{QB}{col}{QE} = vt.{QB}{col}{QE} ) OR ( ut.{QB}{col}{QE} IS NULL AND vt.{QB}{col}{QE} IS NULL) )"))); QueryText.AppendLine(") WHEN MATCHED THEN UPDATE SET"); QueryText.AppendLine($@"{string.Join(", ", UpdateSetColumnNames.Select(col => $"ut.{QB}{col}{QE} = vt.{QB}{col}{QE}"))}"); } else if (ConnectionType == ConnectionManagerType.MySql) { if (IsMariaDb) { QueryText.AppendLine($" ) SELECT * FROM ws ) vt"); } else { QueryText.AppendLine($") vt ( { string.Join(",", SourceColumnNames.Select(col => $"{QB}{col}{QE}"))} )"); } QueryText.Append(" ON "); QueryText.AppendLine(string.Join(Environment.NewLine + " AND ", UpdateJoinColumnNames.Select(col => $@"( ( ut.{QB}{col}{QE} = vt.{QB}{col}{QE} ) OR ( ut.{QB}{col}{QE} IS NULL AND vt.{QB}{col}{QE} IS NULL) )"))); QueryText.AppendLine($@"SET {string.Join(", ", UpdateSetColumnNames.Select(col => $"ut.{QB}{col}{QE} = vt.{QB}{col}{QE}"))}"); } else if (ConnectionType == ConnectionManagerType.Postgres) { QueryText.AppendLine($") vt ( { string.Join(",", SourceColumnNames.Select(col => $"{QB}{col}{QE}"))} )"); QueryText.Append(" WHERE "); QueryText.AppendLine(string.Join(Environment.NewLine + " AND ", UpdateJoinColumnNames.Select(col => $@"( ( ut.{QB}{col}{QE} = vt.{QB}{col}{QE} ) OR ( ut.{QB}{col}{QE} IS NULL AND vt.{QB}{col}{QE} IS NULL) )"))); } else { QueryText.AppendLine($") vt ( { string.Join(",", SourceColumnNames.Select(col => $"{QB}{col}{QE}"))} )"); QueryText.Append(" ON "); QueryText.AppendLine(string.Join(Environment.NewLine + " AND ", UpdateJoinColumnNames.Select(col => $@"( ( ut.{QB}{col}{QE} = vt.{QB}{col}{QE} ) OR ( ut.{QB}{col}{QE} IS NULL AND vt.{QB}{col}{QE} IS NULL) )"))); } }
private void BeginUpdateSql() { if (ConnectionType == ConnectionManagerType.MySql) { QueryText.AppendLine($@"UPDATE {TN.QuotatedFullName} ut"); QueryText.AppendLine("INNER JOIN ("); if (IsMariaDb) { QueryText.AppendLine($@"WITH ws ( {string.Join(", ", SourceColumnNames.Select(col => $"{QB}{col}{QE}"))} ) AS ( VALUES"); } else { QueryText.AppendLine("VALUES"); } } else if (ConnectionType == ConnectionManagerType.Postgres) { QueryText.AppendLine($@"UPDATE {TN.QuotatedFullName} ut"); QueryText.AppendLine($@"SET {string.Join(", ", UpdateSetColumnNames.Select(col => $"{QB}{col}{QE} = vt.{QB}{col}{QE}"))}"); QueryText.AppendLine($@"FROM ("); QueryText.AppendLine("VALUES"); } //https://www.orafaq.com/node/2450 else if (ConnectionType == ConnectionManagerType.Oracle) { QueryText.AppendLine($@"MERGE INTO {TN.QuotatedFullName} ut"); QueryText.AppendLine($@"USING ("); } else { QueryText.AppendLine($@"UPDATE ut"); QueryText.AppendLine($@"SET {string.Join(", ", UpdateSetColumnNames.Select(col => $"ut.{QB}{col}{QE} = vt.{QB}{col}{QE}"))}"); QueryText.AppendLine($@"FROM {TN.QuotatedFullName} ut"); QueryText.AppendLine("INNER JOIN ("); QueryText.AppendLine("VALUES"); } }