mysql mariadb update join SQL 문법 쉬운 설명

mysql mariadb update join SQL 문법 쉬운 설명

mysql이나 mariadb에서 update 하실때 join해서 update 하는 방법에 대해서 쉽고 간단하게 설명을 드리도록 하겠습니다. 기존에 오라클이나 PG등을 사용하신분들은 mysql/mariadb에서 하시던데로 update join을 하면 잘 안되시는걸 경험하셨을겁니다. 간단하게, 쉽게 설명을 드리도록 할께요.

Mysql/Mariadb update join

우선 아래와 같이 실습을 위해 두개의 테이블을 생성 했습니다.
테이블 데이터를 먼저 확인 해보겠습니다.

up_students 테이블 데이터

위 테이블은 학생테이블 입니다. up_students 테이블 이구요.
지금 그림과 같이 major_title과 major_title_prfs_name 컬럼이 null로 채워져 있는데, 이걸 아래 up_major 테이블과 join하여 업데이트 해보도록 하겠습니다.

up_major 테이블 데이터

위 데이터를 보시면 major_id가 1인 초능력학과가 있고, 해당 학과의 대표교수이름은 울트라맨이죠.
그럼 다시 위 테이블을 보시면서 설명을 드릴께요.
아래 up_students와 up_major를 join해서  up_major정보를 up_students에 update 하도록 하겠습니다.

 

 

 

Mysql/Mariadb update join 기본 문법 설명

두 컬럼을 한번에 할수도 있고, 하나씩 할수도 있습니다. 먼저 하나씩 하는 문법은 다음과 같습니다.

update class.up_students s
    join class.up_major m
    on s.major_id = m.major_id
set s.major_title = m.major_title;

먼저 update를 쓰고, update 할 테이블인 up_students를 써줍니다. 그리고 select에서 join 하는것과 같이 테이블 Alias를 s 라고 기재하고, join 키워드 입력 후 join 할 대상 테이블인 up_major를 Alias와 함께 써줍니다. 그리고 아래 on절에 조인키를 명시하시면 됩니다.

그 후에, set으로 어떤 컬럼에 어떤 컬럼값을 받겠다 써주면 되죠.
위에서는 s.major_title컬럼에 m.major_title 값을 update 하겠다는 의미가 됩니다. 즉, s인 students 테이블에 m인 major 테이블의 major_title값을 넣어주는것이죠.

그리고나서 up_students 테이블을 다시 select 해보도록 하겠습니다.

update 후 up_students 테이블의 값

잘 업데이트 되어 있는걸 확인 할 수 있습니다.
그럼 이번에는 두개 컬럼을 한번에 update 하는걸 해볼께요!
SQL은 다음과 같이 작성 하시면 됩니다.

update class.up_students s
    join class.up_major m
    on s.major_id = m.major_id
set s.major_title           = m.major_title
  , s.major_title_prfs_name = m.major_title_prfs_name;

위에서 하나만 업데이트 하는것과 다 똑같은데, set 절에 ,로 한줄이 더 추가 된것을 확인 할 수 있습니다.
잘 되었는지 결과를 보겠습니다.

2개 컬럼값 update 후 up_students 테이블의 값

잘 되었네요!

 

 

 

Mysql/Mariadb update join 문법 select SQL 이용 방법

위와같이 하시면 되는데, 그래도 좀 헷갈리신다면, 아래 방법도 생각해보세요. 만약 아직 이런 문법이 익숙하지 않거나, 이렇게 2개가 아니라, 3개, 4개 join을 해야한다면 아래 방법이 좀 더 쉬울 수 있겠습니다.

먼저 업데이트 칠 테이블과 조인할 테이블을 가지고 평범한 select join SQL을 작성을 합니다.
아래와 같이 말이죠.

select s.student_id
     , m.major_title
     , m.major_title_prfs_name
from class.up_students s
         join class.up_major m
              on s.major_id = m.major_id;

그럼 아래와 같이 결과가 나오게 되겠죠?

select join 한 결과

여기서 주의 하실점은 바로, 업데이트 할 대상테이블의 PK컬럼이 업데이트 할 내용과 함께 출력이 되어야 한다는점 입니다. up_students테이블의 PK는 student_id 이기 때문에 위와 같이 함께 출력을 했습니다.

이상태에서 이렇게 출력한 SQL과 업데이트를 해야하는 up_students 테이블을 update문으로 join 해줍니다. 아래와 같이 작성 하시면 됩니다.

update class.up_students ss join
    (select s.student_id
          , m.major_title
          , m.major_title_prfs_name
     from class.up_students s
              join class.up_major m
                   on s.major_id = m.major_id) mm
    on ss.student_id = mm.student_id
set ss.major_title           = mm.major_title
  , ss.major_title_prfs_name = mm.major_title_prfs_name;

Alias를 써줄때 겹치지 않도록 조심해 주시구요. 겹치더라도 실행은 되지만, 가독성이 안좋아 집니다.
기존것 보다 길어졌지만, 작동하는건 똑같습니다. 위에서 mm Alias를 가진 인파인 뷰를 보시면 바로 위에서 작성한 select SQL과 같다는걸 눈치채셨을겁니다.

이렇게 먼저 select SQL을 작성 후 쉽게 업데이트를 하실 수 있습니다.
참고하시기 바랍니다.

감사합니다.

2021.06.03 - [Database/mariaDB administrator] - [mysql 독학] 독학으로 예제따라 익히는 mysql/mariadb SQL 입문서 출간

[mysql 독학] 독학으로 예제따라 익히는 mysql/mariadb SQL 입문서 출간

2021.03.18 - [Database/mariaDB administrator] - replace into & insert into 차이점, 주의할점이 무엇인지 쉽고 간단하게 확인하기

replace into & insert into 차이점, 주의할점이 무엇인지 쉽고 간단하게 확인하기

2021.01.13 - [Database/mariaDB administrator] - Mysql Mariadb full-text 풀텍스트 검색 방법 자세히 알아보기

Mysql Mariadb full-text 풀텍스트 검색 방법 자세히 알아보기

 

by.sTricky