By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) Mar 4 2020 00:59:26 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
create table t1(id_val Integer,ver varchar(50),caracter varchar(50))
create table t2 (frase varchar(5))
insert into t2 values('x');
insert into t2 values('y');
insert into t2 values('z');
3 rows affected
insert into t1 values(1,'abc','abc');
insert into t1 values(1,'abc','3');
insert into t1 values(1,'abc','x');
insert into t1 values(1,'abc','2');
insert into t1 values(1,'abc','y');
insert into t1 values(2,'def','def');
insert into t1 values(2,'def','5');
insert into t1 values(2,'def','y');
insert into t1 values(2,'def','9');
insert into t1 values(2,'def','z');
10 rows affected
select * from
(select id_val,ver,caracter,lead(caracter) over (order by id_val) lg from t1) tab,t1
where lg in (select frase from t2)
and tab.caracter = t1.caracter
| id_val | ver | caracter | lg | id_val | ver | caracter |
|---|---|---|---|---|---|---|
| 1 | abc | 3 | x | 1 | abc | 3 |
| 1 | abc | 2 | y | 1 | abc | 2 |
| 2 | def | 5 | y | 2 | def | 5 |
| 2 | def | 9 | z | 2 | def | 9 |
--Before update
select * from t1;
| id_val | ver | caracter |
|---|---|---|
| 1 | abc | abc |
| 1 | abc | 3 |
| 1 | abc | x |
| 1 | abc | 2 |
| 1 | abc | y |
| 2 | def | def |
| 2 | def | 5 |
| 2 | def | y |
| 2 | def | 9 |
| 2 | def | z |
update t1 set t1.caracter = concat(t1.caracter,lg)
from
(select id_val,ver,caracter,lead(caracter) over (order by id_val) lg from t1) tab,t1
where lg in (select frase from t2)
and tab.caracter = t1.caracter;
delete from t1 where caracter in (select frase from t2);
8 rows affected
--After update
select * from t1;
| id_val | ver | caracter |
|---|---|---|
| 1 | abc | abc |
| 1 | abc | 3x |
| 1 | abc | 2y |
| 2 | def | def |
| 2 | def | 5y |
| 2 | def | 9z |