两张表里的数据类型啥的都一样,现在要合并表t1 到 t2,满足下面规则:
t1表里面有值的替换掉t2表里的NULL部分,t2表里面不为NULL的值保留或者,
替换t1表里面的一列到t2表里面的一列
要替换掉slope的值,我的命令是
replace into t2(slope) values ( select slope from t1);
但语法不对哈,初学者,请见谅阿~
t1表里面有值的替换掉t2表里的NULL部分,t2表里面不为NULL的值保留或者,
替换t1表里面的一列到t2表里面的一列
要替换掉slope的值,我的命令是
replace into t2(slope) values ( select slope from t1);
但语法不对哈,初学者,请见谅阿~
update t2
set slope=t1.slope
from t1
where t1.ID=t2.ID--可找出關連的列名
t1(slope1, slope2, slope3..)
t2(slope1, slope2, slope3..)
我想把t2里面的slope1替换成t1里面的slope1, 当然最好能直接把t1并到t2里面,把t2不为NULL的替换掉,为NULL的保留。
update t2
set t2.slope=t1.slope
from t1
join t2 on t1.ID=t2.ID--可找出關連的列名
where t2.slope is NULL
--建议先select确认结果再update
--select *
update t2 set t2.slope=t1.slope
from t1 join t2 on t1.ID=t2.ID--t1和t2的关联字段
where t2.slope not is NULL
update t2 set slope= t1.slope from t1 where t1.date = t2.date and t1.ticker = t2.ticker ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from t1 where t1.date = t2.date' at line 1
我也不知道为啥2楼的语法不对~
from t1,t2 where t1.date = t2.date and t1.ticker = t2.ticker ;--建议使用别名
update b set b.slope = case when b.slope is null then b.slope else a.slope end --NULL 值保留否则更新
from t1 a,t2 b where a.date = b.date and a.ticker = b.ticker ;code]
from t1,t2 where t1.date = t2.date and t1.ticker = t2.ticker ;--建议使用别名
update b set b.slope = case when b.slope is null then b.slope else a.slope end --NULL 值保留否则更新
from t1 a,t2 b where a.date = b.date and a.ticker = b.ticker ;