SQLite Update - 전체
SQLite Update - 단일건

using System;


class Program
{
    static void Main()
    {
        string dbPath = "database_all_total.db"; // SQLite 데이터베이스 파일 경로
        string connectionString = $"Data Source={dbPath};Version=3;";

        using (var connection = new SQLiteConnection(connectionString))
        {
            connection.Open();

            // 한 번의 UPDATE로 변환 실행
            string updateQuery = @"
                UPDATE ANESTATEAPTALL
                SET 
                    MainNumber = CAST(MainNumber AS INTEGER),
                    SubNumber = CAST(SubNumber AS INTEGER);";

            using (var command = new SQLiteCommand(updateQuery, connection))
            {
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"업데이트 완료! 변경된 행: {rowsAffected}");
            }
        }
    }
}
using System;
using System.Text;

class Program
{
    static void Main()
    {
        string dbPath = "database_all_total.db"; // SQLite 데이터베이스 파일 경로
        string connectionString = $"Data Source={dbPath};Version=3;";

        using (var connection = new SQLiteConnection(connectionString))
        {
            connection.Open();

            string selectQuery = "SELECT MainNumber, SubNumber FROM ANESTATEAPTALL;";
            using (var selectCommand = new SQLiteCommand(selectQuery, connection))
            using (var reader = selectCommand.ExecuteReader())
            {
                StringBuilder updateQuery = new StringBuilder("UPDATE ANESTATEAPTALL SET ");

                // MainNumber 변환 SQL
                updateQuery.Append("MainNumber = CASE ");
                while (reader.Read())
                {
                    string mainNumber = reader["MainNumber"].ToString();
                    string subNumber = reader["SubNumber"].ToString();

                    int newMainNumber = ConvertToInt(mainNumber);
                    int newSubNumber = ConvertToInt(subNumber);

                    updateQuery.Append($"WHEN MainNumber = '{mainNumber}' THEN '{newMainNumber}' ");
                }
                updateQuery.Append("ELSE MainNumber END, ");

                // SubNumber 변환 SQL
                updateQuery.Append("SubNumber = CASE ");
                reader.Close();
                using (var reader2 = selectCommand.ExecuteReader())
                {
                    while (reader2.Read())
                    {
                        string mainNumber = reader2["MainNumber"].ToString();
                        string subNumber = reader2["SubNumber"].ToString();

                        int newSubNumber = ConvertToInt(subNumber);

                        updateQuery.Append($"WHEN SubNumber = '{subNumber}' THEN '{newSubNumber}' ");
                    }
                }
                updateQuery.Append("ELSE SubNumber END;");

                using (var updateCommand = new SQLiteCommand(updateQuery.ToString(), connection))
                {
                    updateCommand.ExecuteNonQuery();
                }

                Console.WriteLine("한 번에 업데이트 완료!");
            }
        }
    }

    static int ConvertToInt(string input)
    {
        return int.TryParse(input, out int result) ? result : 0;
    }
}
저작자표시 (새창열림)

'데이타베이스 > CHAT.GPT' 카테고리의 다른 글

INSERT ALL 구문은 하나의 트랜잭션으로 처리되므로 성능상 유리합니다.  (0) 2025.05.17
ISAM(Indexed Sequential Access Method) 파일과 Journal File 비교  (0) 2025.03.11
(x1...) 표시는 SQLite가 인덱스를 생성하는 과정에서 발생하는 출력일 가능성이 높음.  (1) 2025.02.20
2020.01 - 2025.01 까지 국토교통부 실거래자료 입력후에 분석해본 통계결과  (1) 2025.01.28

+ Recent posts

티스토리툴바