mysql update 连表举例详讲

和select 一样 update连表查询也有 inner 内联更新,left 左,right 右:

举例:

---

1.创建两个表,并填上数据

CREATE TABLE `student` (
     `id` bigint(20) NOT NULL,
     `class_id` bigint(20) ,
      `class_name` VARCHAR(10),
       `student_name`      VARCHAR(10)    
) ;

CREATE TABLE `class` (
       `id` bigint(20) NOT NULL,
       `class_name` VARCHAR(10)
        `student_name`      VARCHAR(10)
) ;

---

2.执行不同连表更新

# inner-更新双方对应的
update   student a
INNER JOIN class b
on a.class_id = b.id
set a.class_name ='111',
b.student_name ='222'

---

# left-更新全左表 与 对应右表
update   student a
left JOIN class b
on a.class_id = b.id
set a.class_name ='111',
b.student_name ='222'

---

# right-更新全右表 与 对应左表
update   student a
right JOIN class b
on a.class_id = b.id
set a.class_name ='111',
b.student_name ='222'

---

# 跟新右表中没有与左表对应的
update   student a
right JOIN class b
on a.class_id = b.id
set b.student_name ='222'
where a.class_id is  null;

---

#inner连表对应更新(跟新相互对应的,其他不变)
update   student a
INNER JOIN class b
on a.class_id = b.id
set a.class_name = b.class_name,
b.student_name =a.student_name;

---

#left连表对应更新( 跟新与右表对应的全左表数据,没有为null,跟新与左表对应的右表数据,没有不变) -- right 同理
update   student a
left JOIN class b
on a.class_id = b.id
set a.class_name = b.class_name,
b.student_name =a.student_name;

--原来:

--执行后: