SQL怎么把一个表字段信息(ID)修改为对应另一个表的内容
A表、B表都有address_ID这一列，B表中的addressname是address_ID对应的具体内容。请问，如何将A表中的address_ID改为B表中对应的addressname?
1楼、2楼你们的回答确定是sql sever上的语句吗？？
最新回答
淡忘呐情。
2024-11-07 19:56:52
-- 先更改A表中列的数据类型
ALTER TABLE A ALTER COLUMN address_ID VARCHAR(128)
-- 开始联接查询,条件更新
UPDATE a
SET a.address_ID = b.addressname
FROM a, b
WHERE a.address_ID = b.address_ID
SELECT * FROM a
巅峰小学生
2024-11-07 20:44:26
update A set A.address_id = (select B.addressname from B where B.address_id = A.address_id)
执行之前，确认A表address_id字段的类型和长度。
ヽ冷艳°皇后
2024-11-07 15:13:38
执行语句前最好检查a.address_id 的长度是否和b.addressname一致，最好大于等于后者，这样转换后信息不丢失。
另外，要求a.address_id为字符型。
追问
update A set A.address_id = B.addressname where A.address_id=B.address_id
为什么报错啊
能留个联系方式吗 详谈~~
追答
不好意思，没验证。现在把经过验证的给你。在sql2000，2005，2008上通过
UPDATE a
SET address_id = c.addressname
FROM a INNER JOIN
(SELECT address_id, addressname
FROM b) AS c ON a.address_id = c.address_id
绝对不报错。
注意a表address_id 为字符型，且宽度足够
一澜冬雪
2024-11-07 15:08:18
深秋叶落黄
2024-11-07 10:49:07
select A.address_ID,B.addressname from A,B where A.address_ID = B.address_ID
go
--真正update表A
alter table A alter column address_ID varchar(128)
update A set A.address_id = B.addressname where A.address_id=B.addree_id
追问
update A set A.address_id = B.addressname where A.address_id=B.address_id
为什么报错啊
能留个联系方式吗 ？ 详谈~~
追答
我晕，纯属失误，这句应该没问题了
update a set a.address_ID = b.addressname from a,b where a.address_ID = b.address_ID
热门标签
