I have the table_A with some text and I need to select a new text from table_b and replace on table_a.
This is the table_a:
select 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. {T3} Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. {T1} Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. {T2} Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. {T4} Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.'::text as textvalue
This is the table_b:
select '{T1}' as find, 'Test 1a Test 1b Test 1c' as newvalue
union all
select '{T2}' as find, 'Test 2a Test 2b Test 2c' as newvalue
union all
select '{T3}' as find, 'Test 3a Test 3b Test 3c' as newvalue
union all
select '{T4}' as find, 'Test 4a Test 4b Test 4c' as newvalue
order by find
What I've done so far but without success:
select z.newtextvalue
from
(select t2.find, t2.newvalue, t1.textvalue, replace(lag(replace(t1.textvalue,t2.find,t2.newvalue)) over(order by t2.find),t2.find,t2.newvalue) as newtextvalue
from
(select 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. {T3} Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. {T1} Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. {T2} Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. {T4} Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.'::text as textvalue) as t1,
(select '{T1}' as find, 'Test 1a Test 1b Test 1c' as newvalue
union
select '{T2}' as find, 'Test 2a Test 2b Test 2c' as newvalue
union
select '{T3}' as find, 'Test 3a Test 3b Test 3c' as newvalue
union
select '{T4}' as find, 'Test 4a Test 4b Test 4c' as newvalue
order by find) as t2) as z
order by z.find desc limit 1
The bad result:
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Test 3a Test 3b Test 3c Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. {T1} Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. {T2} Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Test 4a Test 4b Test 4c Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
And the expected result:
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Test 3a, Test 3b, Test 3c Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Test 1a, Test 1b, Test 1c Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Test 2a, Test 2b, Test 2c Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Test 4a, Test 4b, Test 4c Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
Any suggestions / corrections are welcome!
LAG()is only going to take into account the prior row. So for{T4}you will only see{T4}and from your LAG(){T3}. Remove yourLIMIT 1. Instead you may need to use a recursive CTE here so you can iterate for each find/replace and update the string. - JNevill