Skip to content
The Chat Archive
gpt-4o-2024-08-06· OpenAI WildChat-4.8M (AI2)

모든 행을 업데이트 하는 SQL 쿼리를 작성하고 싶습니다. 현재 테이블 구조는 다음과 같습니다. 컬럼 타입 설명 ------------ -------…

  1. User

    모든 행을 업데이트 하는 SQL 쿼리를 작성하고 싶습니다. 현재 테이블 구조는 다음과 같습니다.

    컬럼타입설명
    idInteger공지사항 번호
    categoryText공지사항 분류 (예: 학사, 장학, 비교과, 기타)
    departmentText공지 부서
    titleText공지사항 제목
    contentText공지사항 내용
    urlText공지사항 링크
    dateText공지사항 작성일

    하고 싶은 작업은 모든 행의 id 컬럼을 업데이트 하는 것입니다. 모든 url 컬럼의 값은 https://www.ajou.ac.kr/kr/ajou/notice.do?articleNo=326368&mode=view와 같은 형식으로 되어 있습니다. 여기에서 articleNo에 해당하는 326368 값을 가져와서 id 값으로 설정하고 싶습니다. 이걸 SQL 쿼리로 작성할 수 있을까요?

  2. Assistant

    네, SQL 쿼리를 통해 모든 행의 id 컬럼을 업데이트할 수 있습니다. url에서 articleNo 값을 파싱하여 id 컬럼에 설정하기 위해서는, 정규 표현식 또는 문자열 함수를 사용할 수 있습니다. 다양한 데이터베이스 시스템에서 제공하는 함수들이 다를 수 있기 때문에, 대표적인 몇 가지 예를 들어보겠습니다.

    MySQL의 경우

    MySQL에서 SUBSTRING_INDEX와 SUBSTRING 함수를 사용하여 원하는 값을 추출할 수 있습니다.

    UPDATE table_name
    SET id = SUBSTRING_INDEX(SUBSTRING_INDEX(url, 'articleNo=', -1), '&', 1);
    

    PostgreSQL의 경우

    PostgreSQL에서는 regexp_matches 함수를 통해 정규 표현식을 사용할 수 있습니다.

    UPDATE table_name
    SET id = (regexp_matches(url, 'articleNo=([0-9]+)', 'g'))[1]::integer;
    

    SQLite의 경우

    SQLite는 내장 함수로 정규 표현식을 지원하지 않지만, instr와 substr 함수를 조합하여 문자열을 처리할 수 있습니다.

    UPDATE table_name
    SET id = CAST(SUBSTR(url, INSTR(url, 'articleNo=') + 10, 
                         INSTR(SUBSTR(url, INSTR(url, 'articleNo=') + 10), '&') - 1) AS INTEGER);
    

    이 쿼리들은 table_name을 실제 테이블 이름으로 대체해야 합니다. 데이터베이스 시스템에 맞는 쿼리를 사용하세요. 해당 방법들이 정규 표현식을 지원하거나 다른 유사한 함수가 있을 경우 더 정확하고 간단하게 처리할 수 있습니다. 각 데이터베이스가 제공하는 문서를 참고하여 맞춤 설정을 하시기 바랍니다.

  3. User

    SQLite로 작업을 할겁니다. 작성해주신 쿼리에 대해 더 상세히 분석해서 설명해주세요.

  4. Assistant

    물론입니다. SQLite로 url에서 articleNo 값을 추출하여 id 컬럼을 업데이트하기 위한 쿼리는 다음과 같습니다:

    UPDATE table_name
    SET id = CAST(
        SUBSTR(
            url, 
            INSTR(url, 'articleNo=') + 10, 
            INSTR(SUBSTR(url, INSTR(url, 'articleNo=') + 10), '&') - 1
        ) AS INTEGER
    );
    

    이 쿼리의 각 부분을 분석해보겠습니다:

    1. INSTR(url, 'articleNo='):

      • INSTR 함수는 주어진 문자열 안에서 특정 부분 문자열의 시작 위치를 반환합니다. 이 경우에는 'articleNo='라는 부분 문자열의 시작 위치를 찾습니다.
      • 예를 들어, https://www.ajou.ac.kr/kr/ajou/notice.do?articleNo=326368&mode=view라는 URL에서는 'articleNo='가 48번째 위치부터 시작합니다.
    2. INSTR(url, 'articleNo=') + 10:

      • 'articleNo='라는 문자열의 길이는 9이므로, + 10으로 설정하여 실제 숫자 값이 시작되는 위치를 찾습니다. 예제의 경우 326368이라는 숫자는 58번째 위치에서 시작됩니다.
    3. SUBSTR(url, INSTR(url, 'articleNo=') + 10):

      • SUBSTR 함수는 문자열의 특정 위치에서 시작하는 부분 문자열을 반환하는데, 여기서는 articleNo= 다음부터 시작하는 부분 문자열을 반환합니다. 이 결과로 326368&mode=view와 같은 문자열이 됩니다.
    4. INSTR(SUBSTR(...), '&'):

      • 이 부분은 articleNo 값이 끝나는 위치를 찾기 위한 것으로, 위에서 얻은 부분 문자열에서 '&'가 처음 나타나는 위치를 찾습니다. 예제의 경우 326368&mode=view에서 '&'는 7번째 위치에 있습니다.
    5. SUBSTR(url, INSTR(url, 'articleNo=') + 10, INSTR(SUBSTR(url, INSTR(url, 'articleNo=') + 10), '&') - 1):

      • 이제 SUBSTR 함수는 시작 위치부터 '&'가 나타나기 전까지의 부분 문자열을 반환합니다. 이 부분이 실제로 articleNo 값인 326368을 추출해냅니다.
    6. CAST(... AS INTEGER):

      • 마지막으로, 추출한 문자열 326368을 정수로 변환하여 id 컬럼에 저장합니다.

    이 쿼리는 각 행의 url에서 articleNo 값을 파싱하여 id 컬럼에 설정합니다. table_name은 실제 테이블의 이름으로 대체해야 합니다. SQLite의 함수 제약 내에서 문자열을 조작하여 원하는 값만 추출할 수 있는 방법입니다.