在sql-server中,您可以使用cte + select data first
然后对其进行更新。
CREATE TABLE HOTELS
([NAME] varchar(14))
;
INSERT INTO HOTELS
([NAME])
VALUES
('ABCDEFG'),('HIJKLMOP')
;
GO
with cte as (
select top 2 NAME,SUBSTRING(Name,1,5) + 'xxxxx' + SUBSTRING(Name,LEN(Name) - 2,LEN(Name)) AS column1
from [HOTELS]
)
update cte set NAME = column1;
GO
2 rows affected
select * from HOTELS;
| NAME |
| :------------ |
| ABCDExxxxxEFG |
| HIJKLxxxxxMOP |
db 提琴here
编辑oracle版本
CREATE TABLE Table1
("NAME" varchar2(80))
;
✓
INSERT ALL
INTO Table1 ("NAME")
VALUES ('ABCDEFG')
INTO Table1 ("NAME")
VALUES ('HIJKLMOP')
SELECT * FROM dual
;
2 rows affected
update Table1
set name = SUBSTR(Name,5) || 'xxxxx' || SUBSTR(Name,LENGTH(Name) - 2,LENGTH(Name)-1)
where rownum <= 2
2 rows affected
select * from Table1
| NAME |
| :------------ |
| ABCDExxxxxEFG |
| HIJKLxxxxxMOP |
db 提琴here
,
在子查询中,您将前2个值的限制更改为1
update top 2 [Travel].[dbo].[HOTELS]
set [Travel].[dbo].[HOTELS].NAME = (
select top 1 SUBSTRING(Name,LEN(Name)) AS column1
from [Travel].[dbo].[HOTELS]
where [Travel].[dbo].[HOTELS].id = 1415
)
如果您不希望where条件,请不要使用它,例如:
update top 2 [Travel].[dbo].[HOTELS]
set [Travel].[dbo].[HOTELS].NAME = (
select top 1 SUBSTRING(Name,LEN(Name)) AS column1
from [Travel].[dbo].[HOTELS]
)
本文链接:https://www.f2er.com/3161631.html