mysql updata 两个表 sql语句怎么写

Question

表1
id title cid pid
1 a 11 空
2 b 12 空
3 c 11 空
4 d 12 空
5 e 11 空

表2
id pid
11 5
12 6

表1的cid就是表2的id，现在要根据表1的cid的值，通过表2的id与pid对应关系，来更新表1的pid值。
sql语句应该怎么写？

最终结果

表1
id title cid pid
1 a 11 5
2 b 12 6
3 c 11 5
4 d 12 6
5 e 11 5

Answer 1

[SQL]update t1,t2 set t1.pid = t2.pid where t1.cid= t2.id

受影响的行: 0
时间: 0.002s

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

BEGIN
declare c_id int;
declare p_id int;
declare done int default FALSE;
declare v_cur CURSOR for select t2.id as c_id, t2.pid as p_id from t1,t2 where t1.cid = t2.id;
declare continue HANDLER for not found set done = TRUE;

open v_cur;
read_loop:LOOP

    fetch v_cur into  c_id,p_id;
update t1 set pid = p_id  where  cid = c_id;
IF done THEN 
    LEAVE read_loop;    
 END IF;

END LOOP read_loop;
COMMIT;
CLOSE v_cur;
END;