update b set pero=a.per from a join b on a.id=b.id where a.per between %1 and 5%
--纯粹的猜测update b set pero=a.per from a join b on a.id=b.id where a.per between '%1' and '5%'
declare @A表 table (id int,per varchar(4)) insert into @A表 select 1,'5%' union all select 2,'15%' union all select 3,'85%'declare @B表 table (id int,pero varchar(4)) insert into @B表 select 1,null union all select 2,null union all select 3,nullUPDATE @B表 SET pero = LTRIM(100 - REPLACE(a.per, '%', '')) + '%' FROM @B表 b LEFT JOIN @A表 a ON b.id = a.id
SELECT * FROM @B表 /* id pero ----------- ---- 1 95% 2 85% 3 15% */
ERROR: An operand of an arithmetic operation or an operand of a function that requires a number is not a number.DB2 SQL Error: SQLCODE=-402, SQLSTATE=42819, SQLERRMC=-, DRIVER=3.57.82 Error Code: -402还要考虑值为空的情况 如果为空就不更新
A表有个字段per记录百分比 例如5% 现在需要一个SQL将B表的一个字段pero更新为(1-5%)怎么写SQL呢 关联条件就是A和B有一个字段是相同的-->update b set pero = '1-5%' from a , b where a.id = b.id and b.per = '5%'
在sql server 中这样是没有错误的 declare @A表 table (id int,per varchar(4)) insert into @A表 select 1,'5%' union all select 2,'15%' union all select 3,'85%' UNION ALL SELECT 4,'' UNION ALL SELECT 6,nulldeclare @B表 table (id int,pero varchar(4)) insert into @B表 select 1,null union all select 2,null union all select 3,NULL UNION ALL SELECT 4,NULL UNION ALL SELECT 5,NULL UNION ALL SELECT 6,nullUPDATE @B表 SET pero = LTRIM(100 - REPLACE(a.per, '%', '')) + '%' FROM @B表 b LEFT JOIN @A表 a ON b.id = a.id
SELECT * FROM @B表 /* id pero ----------- ---- 1 95% 2 85% 3 15% 4 100% 5 NULL 6 NULL */你是放在DB2里运行的吗?
--纯粹的猜测update b set pero=a.per from a join b on a.id=b.id where a.per between '%1' and '5%'
declare @A表 table (id int,per varchar(4))
insert into @A表
select 1,'5%' union all
select 2,'15%' union all
select 3,'85%'declare @B表 table (id int,pero varchar(4))
insert into @B表
select 1,null union all
select 2,null union all
select 3,nullUPDATE @B表
SET pero = LTRIM(100 - REPLACE(a.per, '%', '')) + '%'
FROM @B表 b
LEFT JOIN @A表 a ON b.id = a.id
SELECT * FROM @B表
/*
id pero
----------- ----
1 95%
2 85%
3 15%
*/
闭SQL大版1周
倒分贴:
http://topic.csdn.net/u/20111117/17/03533dcf-f337-4ec9-8c4a-e831fa6e2f7d.html
再倒分關1年
a function that requires a number is not a number.DB2 SQL
Error: SQLCODE=-402, SQLSTATE=42819, SQLERRMC=-, DRIVER=3.57.82
Error
Code: -402还要考虑值为空的情况 如果为空就不更新
declare @A表 table (id int,per varchar(4))
insert into @A表
select 1,'5%' union all
select 2,'15%' union all
select 3,'85%' UNION ALL
SELECT 4,'' UNION ALL
SELECT 6,nulldeclare @B表 table (id int,pero varchar(4))
insert into @B表
select 1,null union all
select 2,null union all
select 3,NULL UNION ALL
SELECT 4,NULL UNION ALL
SELECT 5,NULL UNION ALL
SELECT 6,nullUPDATE @B表
SET pero = LTRIM(100 - REPLACE(a.per, '%', '')) + '%'
FROM @B表 b
LEFT JOIN @A表 a ON b.id = a.id
SELECT * FROM @B表
/*
id pero
----------- ----
1 95%
2 85%
3 15%
4 100%
5 NULL
6 NULL
*/你是放在DB2里运行的吗?
提示必须声明服务器: 消息 137,级别 15,状态 2,行 2
必须声明变量 '@B表'。
前边都执行成功了 SQL2000