20230110

string whereUp;
string sql_upCase;

public void Main()
{
Console.WriteLine("--- STRAT ---\r\n" + DateTime.Now + "\r\n");

Program pg = new Program();
DataTable dt = new DataTable();

pg.A_TableMarge(); //MARGE実行
pg.A_TableUpdate(); //UPDATE実行
pg.TableDistinct_ALL();//DISTINCT実行(全行)
pg.TableDistinct_Parts();//DISTINCT実行(列目直書き)

Console.WriteLine(DateTime.Now + "\r\n\"--- END ---\r\n");
Console.ReadKey();
}

public void TableDistinct_ALL()
{
repTN = "DEPT_BK R";
xbbTN = "DEPT X";

foreach (DataColumn dc in dt.Columns)
{
Console.WriteLine("■列名：" + dc.ColumnName);

strSQL_DIST = "select distinct " + dc.ColumnName + " from " + repTN;
strSql = strSQL_DIST;
//DBA_non_dt(strSQL); //SQL実行
dt = DBA(strSql); //SQL実行

Console.WriteLine(dc.ColumnName + ":" + dt.Rows.Count + "件が一意のデータ\r\n");

}
}

public void TableDistinct_Parts()
{
repTN = "DEPT_BK R";
xbbTN = "DEPT X";

//カラム名を直書き
strSQL_DIST = "select distinct " + "DEPTNO,DNAME,LOC,KOUSHINBI" + " from " + repTN;
strSql = strSQL_DIST;
dt = DBA(strSql); //SQL実行

Console.WriteLine(dc.ColumnName + ":" + dt.Rows.Count + "件が一意のデータ\r\n");

}

public void A_TableMarge()
{
DataTable dt = new DataTable();

repTN = "DEPT_BK R";
xbbTN = "DEPT X";

col1 = "DEPTNO";
col2 = "DNAME";
col3 = "REGISTEDDT";

whereON =
"R.UPDATEEDDT > X.UPDATEEDDT" +
" and R." + col1 + " = X." + col1 +
" and R." + col2 + " = X." + col2 +
" and R." + col3 + " = X." + col3;

foreach (DataColumn dc in dt.Columns)
{
strSQL_MRG =
"merge into " + repTN +//strign dbkTN
" using " + xbbTN +//dTN
" on (" + whereON + ")" + //sql_ON
" when matched then update set " +
"R." + dc.ColumnName + " = X." + dc.ColumnName;// //sql_UPSET = DBK.dc.ColumnName + D.dc.ColumnName

strSql = strSQL_MRG;
dt = DBA(strSQL);
}
}

public void A_TableUpdate()
{
DataTable dt = new DataTable();

repTN = "DEPT_BK R";
xbbTN = "DEPT X";

col1 = "DEPTNO";
col2 = "DNAME";
col3 = "REGISTEDDT";

whereUp =
"R.UPDATEEDDT < X.UPDATEEDDT" +
" and R." + col1 + " = X." + col1 +
" and R." + col2 + " = X." + col2 +
" and R." + col3 + " = X." + col3;

//update実行
foreach (DataColumn dc in dt.Columns)
{
//string sql_upWhere = " where " + whereUp;
//string sql_upExis = "where exists (select * from " + xbbTN + " where " + whereUp;
sql_upCase =
"case when R.UPDATEEDDT < (select D.UPDATEEDDT from " + xbbTN + " where " + whereUp + ")" +
"then (select D." + dc.ColumnName + " from " + xbbTN + " where " + whereUp + ") END " +
"where exists (select * from " + xbbTN + " where " + whereUp;

strSQL_UP = "update " + repTN + " set R." + dc.ColumnName + " = " + sql_upCase;

strSql = strSQL_UP;
dt = DBA(strSql);
}
}