update pre_forum_attachment set tableid=1 where tid?????;不好意思 我打少了一句
update table set tableid=case right(cast(tid as varchar),1) when 1 then 1 when 2 then 2 when 0 then 0 else xx end更新前建议先备份表中的数据
update table set tableid=case right(cast(tid as varchar),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then 5 when 6 then 6 when 7 then 7 when 8 then 8 when 9 then 9 when 0 then 0 else xx endELSE补些什么的?
select case right(cast(tid as varchar),1) when 1 then 1 when 2 then 2 when 0 then 0 WHEN 3 THEN 3 WHEN 4 THEN 3 WHEN 5 THEN 5 .... ELSE '这里实际上是处理一些异常情况,比如最后以为是字母,应该怎么处理这些' end tableid from table
update table set tableid=case right(cast(tid as varchar),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then 5 when 6 then 6 when 7 then 7 when 8 then 8 when 9 then 9 when 0 then 0 else xx endELSE补些什么的?试试这个:update table set tableid=tid % 10
这是查询?能修改吗?update table set tableid= isnull(tid,0)%10
如果可以确定是不会出现其他异常的话 是否可以留空还是?下面是我补全的,请查看是否正确 update pre_forum_attachment set tableid=case right(cast(tid as varchar),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then 5 when 6 then 6 when 7 then 7 when 8 then 8 when 9 then 9 when 0 then 0 else end
update pre_forum_attachment set tableid=case right(cast(tid as varchar),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then 5 when 6 then 6 when 7 then 7 when 8 then 8 when 9 then 9 when 0 then 0 ELSE 0 end用这个,当出现异常的话,就写死为0,
这是查询?能修改吗?update table set tableid= isnull(tid,0)%10 错误码: 1582 Incorrect parameter count in the call to native function 'isnull'
错误码: 1064 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 'varchar),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then' at line 1
mysql......我这是sqlserver的写法哦update pre_forum_attachment set tableid=case right(cast(tid as varchar(10)),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then 5 when 6 then 6 when 7 then 7 when 8 then 8 when 9 then 9 when 0 then 0 ELSE 0 end 这个试试
早说mysql嘛,mysql估计没有isnull函数,所以他们的报错
错误码: 1064 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 'varchar(10)),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 ' at line 1
mysql里面怎么把数据类型转成字符型?
update pre_forum_attachment set tableid=case right(cast(tid as char(4)),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then 5 when 6 then 6 when 7 then 7 when 8 then 8 when 9 then 9 when 0 then 0 ELSE 0 end这个呢?
(46247 row(s) affected, 50798 warning(s)) Execution Time : 00:00:00:468 Transfer Time : 00:00:00:000 Total Time : 00:00:00:468
/*字段信息 - sql.pre_forum_attachment*/ -------------------------------------------Field Type Collation Null Key Default Extra Privileges Comment --------- --------------------- --------- ------ ------ ------- -------------- ------------------------------- ------- aid mediumint(8) unsigned (NULL) NO PRI (NULL) auto_increment select,insert,update,references tid mediumint(8) unsigned (NULL) NO MUL 0 select,insert,update,references pid int(10) unsigned (NULL) NO MUL 0 select,insert,update,references downloads mediumint(8) (NULL) NO MUL 0 select,insert,update,references uid mediumint(8) unsigned (NULL) NO MUL 0 select,insert,update,references tableid tinyint(1) unsigned (NULL) NO 0 select,insert,update,references /*索引信息 - sql.pre_forum_attachment*/ -------------------------------------------Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -------------------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- pre_forum_attachment 0 PRIMARY 1 aid A 51711 (NULL) (NULL) BTREE pre_forum_attachment 1 tid 1 tid A 10342 (NULL) (NULL) BTREE pre_forum_attachment 1 uid 1 uid A 907 (NULL) (NULL) BTREE pre_forum_attachment 1 dateline 1 downloads A 1034 (NULL) (NULL) BTREE pre_forum_attachment 1 pid 1 pid A 12927 (NULL) (NULL) BTREE /*DDL 信息 - sql.pre_forum_attachment*/ -----------------------------------------Table Create Table -------------------- ---------------------------------------------------------- pre_forum_attachment CREATE TABLE `pre_forum_attachment` ( `aid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `tid` mediumint(8) unsigned NOT NULL DEFAULT '0', `pid` int(10) unsigned NOT NULL DEFAULT '0', `downloads` mediumint(8) NOT NULL DEFAULT '0', `uid` mediumint(8) unsigned NOT NULL DEFAULT '0', `tableid` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`aid`), KEY `tid` (`tid`), KEY `uid` (`uid`), KEY `dateline` (`downloads`), KEY `pid` (`pid`) ) ENGINE=MyISAM AUTO_INCREMENT=59595 DEFAULT CHARSET=gbk
也就是执行成功了?你检查一下数据,warning是什么意思....mysql不熟
没有mysql的环境,如果还报错,试一下用这个: update pre_forum_attachment set tableid=case right(tid ,1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then 5 when 6 then 6 when 7 then 7 when 8 then 8 when 9 then 9 when 0 then 0 ELSE 0 end
select *,
tid % 10
from 表
set tableid=case right(cast(tid as varchar),1) when 1 then 1 when 2 then 2 when 0 then 0 else xx end更新前建议先备份表中的数据
set tableid=case right(cast(tid as varchar),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then 5 when 6 then 6 when 7 then 7 when 8 then 8 when 9 then 9 when 0 then 0 else xx endELSE补些什么的?
tid尾数为1的时候=1
tid尾数为2的时候=2
……
tid尾数为0的时候=0如果位数为3,=3,4=4这些,就补全,
from table
set tableid=case right(cast(tid as varchar),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then 5 when 6 then 6 when 7 then 7 when 8 then 8 when 9 then 9 when 0 then 0 else xx endELSE补些什么的?试试这个:update table
set tableid=tid % 10
这是查询?能修改吗?update table set tableid= isnull(tid,0)%10
update pre_forum_attachment set tableid=case right(cast(tid as varchar),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then 5 when 6 then 6 when 7 then 7 when 8 then 8 when 9 then 9 when 0 then 0 else end
这是查询?能修改吗?update table set tableid= isnull(tid,0)%10 错误码: 1582
Incorrect parameter count in the call to native function 'isnull'
错误码: 1064
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 'varchar),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then' at line 1
这个试试
错误码: 1064
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 'varchar(10)),1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 ' at line 1
Execution Time : 00:00:00:468
Transfer Time : 00:00:00:000
Total Time : 00:00:00:468
/*字段信息 - sql.pre_forum_attachment*/
-------------------------------------------Field Type Collation Null Key Default Extra Privileges Comment
--------- --------------------- --------- ------ ------ ------- -------------- ------------------------------- -------
aid mediumint(8) unsigned (NULL) NO PRI (NULL) auto_increment select,insert,update,references
tid mediumint(8) unsigned (NULL) NO MUL 0 select,insert,update,references
pid int(10) unsigned (NULL) NO MUL 0 select,insert,update,references
downloads mediumint(8) (NULL) NO MUL 0 select,insert,update,references
uid mediumint(8) unsigned (NULL) NO MUL 0 select,insert,update,references
tableid tinyint(1) unsigned (NULL) NO 0 select,insert,update,references /*索引信息 - sql.pre_forum_attachment*/
-------------------------------------------Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
-------------------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- -------
pre_forum_attachment 0 PRIMARY 1 aid A 51711 (NULL) (NULL) BTREE
pre_forum_attachment 1 tid 1 tid A 10342 (NULL) (NULL) BTREE
pre_forum_attachment 1 uid 1 uid A 907 (NULL) (NULL) BTREE
pre_forum_attachment 1 dateline 1 downloads A 1034 (NULL) (NULL) BTREE
pre_forum_attachment 1 pid 1 pid A 12927 (NULL) (NULL) BTREE /*DDL 信息 - sql.pre_forum_attachment*/
-----------------------------------------Table Create Table
-------------------- ----------------------------------------------------------
pre_forum_attachment CREATE TABLE `pre_forum_attachment` (
`aid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`tid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`pid` int(10) unsigned NOT NULL DEFAULT '0',
`downloads` mediumint(8) NOT NULL DEFAULT '0',
`uid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`tableid` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`aid`),
KEY `tid` (`tid`),
KEY `uid` (`uid`),
KEY `dateline` (`downloads`),
KEY `pid` (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=59595 DEFAULT CHARSET=gbk
update pre_forum_attachment set tableid=case right(tid ,1) when 1 then 1 when 2 then 2 when 3 then 3 when 4 then 4 when 5 then 5 when 6 then 6 when 7 then 7 when 8 then 8 when 9 then 9 when 0 then 0 ELSE 0 end