我想修改一个表中的前几条记录
我是这么写的,不对,应该是怎样?
UPDATE YMJ.TXMD_YSBZ01
SET MDBH=RTRIM(MDBH)+'01'
WHERE (SELECT TOP 5 WITH TIES mdbh, kc, fp, ad
FROM ymj.txmd_ysbz01
ORDER BY mdbh DESC)
谢谢指教!
...全文
465打赏收藏
怎样限定一个表中的前几条记录或后几条记录?
我想修改一个表中的前几条记录 我是这么写的,不对,应该是怎样? UPDATE YMJ.TXMD_YSBZ01 SET MDBH=RTRIM(MDBH)+'01' WHERE (SELECT TOP 5 WITH TIES mdbh, kc, fp, ad FROM ymj.txmd_ysbz01 ORDER BY mdbh DESC) 谢谢指教!
语句本身是行的,但TOP 35内不全是或全不是:
SUBSTRING(mdbh, 1, 3) = '141'
------------------------------------------------
SELECT *
FROM ymj.txmd_ysbz01
WHERE (SUBSTRING(mdbh, 1, 3) = '141') AND MDBH IN
(SELECT TOP 35 WITH TIES MDBH
FROM YMJ.TXMD_YSBZ01 ORDER BY MDBH DESC)
--------------------------------------------------------
你若想找出前35个SUBSTRING(mdbh, 1, 3) = '141'的,应改为:
SELECT *
FROM ymj.txmd_ysbz01
WHERE MDBH IN
(SELECT TOP 35 WITH TIES MDBH
FROM YMJ.TXMD_YSBZ01 where SUBSTRING(mdbh, 1, 3) = '141'
ORDER BY MDBH DESC)
两位说的很对,问题解决!
可是我想这样查,为什么不行?:(
SELECT *
FROM ymj.txmd_ysbz01
WHERE (SUBSTRING(mdbh, 1, 3) = '141') AND MDBH IN
(SELECT TOP 35 WITH TIES MDBH
FROM YMJ.TXMD_YSBZ01 ORDER BY MDBH DESC)
UPDATE YMJ.TXMD_YSBZ01
SET MDBH=RTRIM(MDBH)+'01' from ymj.txmd_ysbz01 t1,
(SELECT TOP 5 WITH TIES mdbh, kc, fp, ad
FROM ymj.txmd_ysbz01
ORDER BY mdbh DESC)t2
where ...(如:t1.mdbh=t2.mdbh and ...)