能否用子查询设置SQL默认值？shipment_mst.co_num字段咨询

问题说明 #

尝试为shipment_mst表的co_num字段设置默认值，取值自ssspj_shipment_mst表的userfield2字段，但所有尝试均报错：此上下文不允许子查询，仅允许标量表达式。

两次失败的尝试 #

• 第一次尝试：

ALTER TABLE shipment_mst
ADD CONSTRAINT dfco_num DEFAULT (SELECT userfield2 FROM ssspj_shipment_mst WEHRE shipment_mst.ref_num = ssspj_shipment_mst.shipment_id);

• 第二次尝试：

ALTER TABLE shipment_mst
ADD co_nom nvarchar(10) NOT NULL default (
SELECT userfield2
FROM ssspj_shipment_mst
JOIN shipment_mst
     ON shipment_mst.ref_num = ssspj_shipment_mst.shipment_id)

当前已完成的操作 #

已创建co_num字段，并执行以下UPDATE语句更新已有记录：

UPDATE shipment_mst
SET shipment_mst.co_num = ssspj_shipmen_mst.userfield2
FROM ssspj_shipment_mst
JOIN shipment_mst
     ON shipment_mst.ref_num = ssspj_shipment_mst.shipment_id

疑问 #

上述UPDATE语句仅能更新已有记录，对新增记录无效。请问是否有办法为shipment_mst.co_num设置默认值？还是必须每日更新表或编写存储过程？

内容的提问来源于stack exchange，提问作者Clay