0

I wanna create a triger function to feed a column with a value generated from a concatenation of two other columns with predetermined values. If the value alredy exist i would add a int to the concatanation.

EX: 

column1 -> 'aaa' 
column2 -> 'bbb' 

concated value -> 'aaabbb'

if there's already rows with the values 'aaabbb' and 'aaabbb1', add a int to the value -> 'aaabbb3'.

All the three columns are the type text.

I image the sql would be something like this:

UPDATE formulario.formulario_projetos SET repid = concat(distrito, tipo_ep, SELECT COUNT(repid));

lucazpinheiro
  • 51
  • 2
  • 11
  • 1
    Possibly an [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Why do you want to do this? – sticky bit Apr 10 '19 at 18:38
  • The idea is that i have a table on my db that will receive data of internal projects of my organization. The concat value would serve as a way to informa that more than one project has the same subject. – lucazpinheiro Apr 10 '19 at 18:45
  • Hmm, in many cases instead of copying data from one table to another a view is more appropriate. You could include a `row_number()` there for numbering the subjects. And concatenation of atomic values, hence demormalizing, should also be used with great care. Do that in the application if necessary. – sticky bit Apr 10 '19 at 20:07

1 Answers1

0

Update statement

 UPDATE formulario.formulario_projetos
     SET repid = case when  repid = concat(distrito, tipo_ep) then concat(distrito, tipo_ep) + convert(nvarchar(10), (select count(repid) from  tbl where repid LIKE CONCAT(repid, '%'))) else concat(distrito, tipo_ep) end
Rima
  • 1,447
  • 1
  • 6
  • 12
  • I got the following error with your script: `ERROR: function nvarchar(integer) does not exist LINE 2: ...tipo_ep) then concat(distrito, tipo_ep) + convert(nvarchar(1... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 142` I should have mentioned that all the columns (repid, distrito, tipo_ep) are type 'text' – lucazpinheiro Apr 10 '19 at 18:53
  • This might help you, https://stackoverflow.com/questions/40370117/sql-state-42883-no-function-matches-the-given-name-and-argument-types-but-tha – Rima Apr 10 '19 at 19:19