Doubt - Update SQL Server 2012

Asked

Viewed 171 times

0

When the update below is run for the first time, the field comes out correctly in the way I want it to be below, but if I run it again, it comes out that way. What I want is that regardless of how many times the script is executed, do not duplicate the month or the year.

(WRONG) Tartitulo Strategic Assisted Production October-2017October-2017

(CORRECT) Tartitulo Strategic Assisted Production October-2017

Update script

UPDATE EstoqueTarefa
SET EstTarTitulo = (SELECT CONCAT(EstTarTitulo,DATENAME(MONTH,getdate()),'-',DATEPART(YEAR,getdate()))) 
WHERE EstTarID = 246
sql sql-update sql-server-2012

  • You cannot add a column indicating that the title is up to date?

  • no, no @sorack

  • Give an example of the data with at least 2 records and how they will look after each update. It is impossible to understand what you want by the current description

  • @Sorack, come on, below is the example of a record without the update in the table stocking in the field esttartitulo Example without the update Esttartitulo Strategic Assisted Production is to look like this: Esttartitulo Strategic Assisted Production NOVEMBER - 2017 Example right after the update is running in November, is to look like this: Esttartitulo Strategic Assisted Production DECEMBER - 2017 And so on and so forth.

  • @Sorack, I managed to settle here my dear. Thank you

3 answers

1

You can do it this way too

declare @MesAno varchar(50);
set @MesAno = (SELECT DATENAME(MONTH,getdate()) + '-' + Convert(varchar, DATEPART(YEAR,getdate())) as MesAno)

UPDATE  EstoqueTarefa
SET     EstTarTitulo = EstTarTitulo + @MesAno
WHERE   EstTarID = 246 AND CharIndex(@MesAno, EstTarTitulo)) = 0

0


Declare a support table, populate it and use it to check if the title already has one month included:

DECLARE @meses TABLE(numero INT,
                     nome   VARCHAR(10));
DECLARE @data_base DATE,
        @mes       VARCHAR(10),
        @ano       INT;

SET @data_base = DATEADD(MONTH, 1, GETDATE());

INSERT INTO @meses
VALUES(1, 'JANEIRO'),
      (2, 'FEVEREIRO'),
      (3, 'MARÇO'),
      (4, 'ABRIL'),
      (5, 'MAIOR'),
      (6, 'JUNHO'),
      (7, 'JULHO'),
      (8, 'AGOSTO'),
      (9, 'SETEMBRO'),
      (10, 'OUTUBRO'),
      (11, 'NOVEMBRO'),
      (12, 'DEZEMBRO');

SELECT @mes = m.nome,
       @ano = DATEPART(YEAR, @data_base)
  FROM @meses m
 WHERE m.numero = DATEPART(MONTH, @data_base);

UPDATE et
   SET et.EstTarTitulo = EstTarTitulo + ' ' + @mes + ' - ' + CAST(@ano AS VARCHAR)
  FROM EstoqueTarefa et
 WHERE EstTarID = 246
   AND NOT EXISTS(SELECT 1
                    FROM @meses m
                   WHERE et.EstTarTitulo LIKE '%' + m.nome + '%')

  • syntax error occurred: Msg 102, Level 15, State 1, Line 26 Incorrect syntax next to@mes'.

  • now gave Msg 245, Level 16, State 1, Line 25 Failed to convert the nvarchar value 'Strategic Assisted Production OCTOBER - ' to the int data type. And I couldn’t verify where.

  • blz, did not give the error, but as I am in October, I normally managed the month of November, however, I did a test setting the server date for November to see if it would generate the December and did not generate. NOTE: there in m.numero I put the date add to rename the title pro mes following. DATEPART(MONTH,DATEADD(MONTH,1,getdate()));

  • @Renanbessa there is the error. If you wanted the following month could have said that in the question. I will change to this new information.

  • @Renanbessa changed with this new information.

  • @My young man Sorack, I tested here using the month of November to see if it would generate the title for December, however, not updated, was the same title of November.

  • @Renanbessa did not understand the test you made. You updated first in November and then in December? You want every time you run change the month?

  • no, so: We are in the month of October right? and when generate this script of the month of October, the title goes to Producao Estrategico November. Even then it spawned normally. So I went to test as if I was in the month of November (changing the server date to November) for the title to change to Producao Estrategico December, however, did not generate the title, was the same title of the previous month, in the case November.

  • @Renanbessa So the same record will change the name from October to November?

Show 4 more comments

0

The charindex should solve your problem:

declare @MesAno varchar(50)
select @MesAno = CONCAT(DATENAME(MONTH,getdate()),'-',DATEPART(YEAR,getdate()))
select @MesAnoAnterior = CONCAT(DATENAME(MONTH,DATEADD(month, -1, getdate())),'-',DATEPART(YEAR,DATEADD(month, -1, getdate())))

UPDATE EstoqueTarefa
SET EstTarTitulo = (SELECT CONCAT(REPLACE(EstTarTitulo,CONCAT(' ', @MesAnoAnterior),''), ' ', @MesAno)) 
WHERE EstTarID = 246

So it only updates the column if it does not contain what you want to add. As the proposal is that the script run monthly, you can use the above structure, where:

  • REPLACE(EstTarTitulo,CONCAT(' ', @MesAnoAnterior),'') remove from the title the previous month (if any)
  • (SELECT CONCAT({resultadoDoReplace}, ' ', @MesAno)) updates the value of the title with the current month

  • large @rLinhares, the following error occurred: Msg 4145, Level 15, State 1, Line 3 A non-boolean type expression specified in a context in which a condition is expected, next to ','.

  • Another thing r@Linhares, if I want to run this update in other months, I did not want q to keep repeating the previous month + current month, I wanted for example in November, to be like this:Esttartitulo Assisted Production Strategic November-2017, and not repeating the month of October.

  • @Renanbessa, I corrected the error you mentioned, was wrong :P now let’s think about the generic solution that you can run every month..

  • will you run every month? because an exit would remove the previous month (when it exists) and add the current month.

  • yes my big, will run every month. And how would it be more or less? To rolled, rsrs

  • @Renanbessa, see if now solves..

  • Let’s go continue this discussion in chat.

  • I took the test here and so. I simulated the months of October, November and December. The November spawned normally, however, when I went to generate the December, spawned so. 'Assisted Production Strategic November 2017-December 2017'. That is, did not take the November, but the November took the October

  • @Renanbessa, I did some tests here.. I think the error ta only in the charindex of the end.. a tested again. And it warns because if you solve I need to change the explanation of the answer :P

  • This way if it runs the script for 3 consecutive months the same error will occur.

  • @Well, in that case I saw you took the charindex, but the duplication error came back.

Show 6 more comments

Browser other questions tagged sql sql-update sql-server-2012

You are not signed in. Login or sign up in order to post.