MySQL에서 하나의 쿼리로 여러 테이블 업데이트

관련 테이블의 행을 한 번의 작업으로 함께 변경해야 할 때는 다중 테이블 UPDATE 문을 사용합니다. 예를 들어 책을 대여하면 library의 대여 가능 수량을 줄이는 동시에 stu_book에서 학생에게 배정된 수량을 늘릴 수 있습니다. 조인 조건으로 서로 관련된 행을 찾고, 하나의 문에서 두 테이블의 변경 값을 지정합니다. 단일 문을 사용하면 별도의 업데이트 호출을 줄일 수 있지만, 실행 시간이 짧아지거나 업무 데이터가 정확해진다고 보장하지는 않습니다. 이러한 결과는 스키마, 조건, 인덱스, 트랜잭션 설정, 애플리케이션 요구 사항에 따라 달라집니다.

MySQL에서 한 번의 쿼리로 여러 테이블을 업데이트할 때의 이점

관련된 행을 하나의 문으로 업데이트하면 의도한 관계를 더 명확하게 표현할 수 있고, 애플리케이션 코드가 한쪽 업데이트만 보내고 다른 쪽을 빠뜨릴 가능성도 줄어듭니다. 두 변경이 모두 하나의 문에서 성공하면, 데이터베이스는 트랜잭션 및 스토리지 엔진의 동작에 따라 해당 문에서 발생한 변경을 함께 적용합니다. 애플리케이션 수준에서 더 강한 보장이 필요하다면 적절한 트랜잭션을 사용하고 관련 테이블의 제약 조건과 오류 처리를 확인하세요.

애플리케이션이 같은 데이터베이스에 별도의 업데이트를 보내는 경우 이 방식은 왕복 통신 횟수도 줄일 수 있습니다. 그렇다고 본질적으로 더 빠른 것은 아닙니다. 쿼리 실행 계획, 인덱스, 잠금 경합, 행 수, 네트워크 비용이 성능에 영향을 줍니다. 속도가 중요하다면 실제 워크로드에서 별도의 문을 사용하는 방식과 비교하세요. 핵심 이점은 조율된 변경을 하나의 SQL 문으로 표현하는 것이며, 모든 구성에서 응답 시간이 짧아지거나 일관성이 보장된다는 뜻은 아닙니다.

UPDATE 키워드로 MySQL에서 한 번의 쿼리로 여러 테이블 업데이트하기

MySQL의 다중 테이블 업데이트 형식은 UPDATE 뒤에 대상 테이블을 지정하고, SET에서 각 테이블의 열에 값을 할당하며, 조인 조건으로 관련 행을 선택합니다. UPDATE와 SET은 서로 다른 키워드입니다. “다중 테이블 업데이트”는 하나로 결합된 키워드가 아니라 작업을 가리키는 표현입니다. 일반적인 형식은 UPDATE table1 AS a JOIN table2 AS b ON a.key = b.key SET a.column = value1, b.column = value2 WHERE condition;입니다. 조인 식은 행을 연결하고, WHERE를 사용하면 변경할 일치 행을 추가로 제한할 수 있습니다. 두 테이블에 같은 이름의 열이 있다면 테이블 별칭을 붙여 열을 명확히 지정하세요.

책을 대여하는 상황에서 library.id가 책 식별자이고 stu_book.book_id가 이를 참조한다고 가정해 보겠습니다. 다음 Python 예제는 메모리에서 일치하는 책 한 권과 학생 레코드 하나를 모델링합니다. 의도한 두 수량 변경을 보여 주는 예제이며, MySQL에 연결하거나 SQL을 실행하지는 않습니다.

다음 예제를 실행하세요:

이 섹션의 코드와 표시되는 레이블 또는 출력은 영어로 유지됩니다.

library = {"id": 7, "book_count": 5}
stu_book = {"book_id": 7, "book_count": 0}

if library["id"] == stu_book["book_id"]:
    library["book_count"] -= 2
    stu_book["book_count"] += 2

print(f'library={library["book_count"]}, stu_book={stu_book["book_count"]}')

이에 대응하는 SQL 문은 UPDATE library AS l JOIN stu_book AS s ON l.id = s.book_id SET l.book_count = l.book_count - 2, s.book_count = s.book_count + 2 WHERE l.id = 7;입니다. 일치하는 library 행에서 2를 빼고, 일치하는 stu_book 행에 2를 더합니다. 데이터 모델에 맞게 수량과 조건을 조정하세요. 실제 대여 흐름에 적용할 때는 먼저 library에 충분한 수량이 있고 학생에게 배정하는 것이 유효한지 확인해야 합니다. 이 문서의 SQL은 구문을 보여 주기 위한 예시이며 데이터베이스에서 실행하지 않았으므로, 검증된 MySQL 출력은 없습니다.

다중 테이블 업데이트에서는 조인 결과에 같은 행에 대한 일치 항목이 여러 개 있더라도 각 대상 행을 한 번 업데이트합니다. 의도한 레코드만 선택하도록 조인 키와 조건을 충분히 구체적으로 지정하세요. 여기서 관계는 library.id = stu_book.book_id입니다. 이 예제에서는 library_id 열을 사용하지 않으므로 이를 참조하면 잘못된 조건이 됩니다. 운영 데이터를 변경하는 문을 실행하기 전에 조인과 조건을 검토하세요.

단일 테이블 업데이트와 달리 다중 테이블 업데이트에는 ORDER BY 또는 LIMIT을 사용할 수 없습니다. 대상 행의 수를 제한해야 한다면 적절한 조건이나 다른 트랜잭션 안전 접근법으로 대상 집합을 선택하고 식별한 다음, 해당 절 없이 업데이트하세요. 명령줄 클라이언트의 “영향을 받은 행” 메시지는 실제로 일치하고 변경된 데이터와 클라이언트 동작에 따라 달라지며, 의도한 작업이 올바르게 수행되었다는 증거는 아닙니다. 정확성이 중요하다면 관련 행과 애플리케이션 불변 조건을 확인하세요.