Update multiple rows SQL Server 2008

Question

Good day, I'm trying to update my table. Because there was an error(s) in my website. first Please check my table. (Penilaian_Header)

IdPenilaian | KodePenilaian       |   Nip  | PositionCode | Total
    1613           -----             1603405    P028          0 
    1618           -----             1602999    P028          0 
    1641          PE0001568           603060    P040         35
    1640          PE0001567          1411862    P007         35

as you can see. There are two rows that KodePenilaian empty. So is there any chance to fill it ? so the result will be like this.

IdPenilaian | KodePenilaian       |   Nip  | PositionCode | Total
    1613          PE0001570         1603405     P028          0 
    1618          PE0001569         1602999     P028          0 
    1641          PE0001568           603060    P040         35
    1640          PE0001567          1411862    P007         35

This how i generate KodePenilaian

select case 
    when right(max(KodePenilaian),7) is null then 'PE0000001' 
    else ('PE' + RIGHT('0000000' + cast(right(max(KodePenilaian),7) + 1 as nvarchar),7)) 
    end KodePenilaian from Penilaian_Header

and here is there result when i run it

KodePenilaian
  PE0001569

Thanks, Sorry for my bad english.

MadddinTribleD · Accepted Answer · 2018-01-22 07:30:19Z

1

Not really used to sql server 2008 Try something like this:

update Penilaian_Header pen 
   set pen.KodePenilaian = 
             (select case 
                     when right(max(newKode.KodePenilaian),7) is null then 'PE0000001' 
                     else ('PE' + RIGHT('0000000' + cast(right(max(newKode.KodePenilaian),7) + 1 as nvarchar),7)) 
                     end KodePenilaian
                from Penilaian_Header newKode)
 where pen.KodePenilain = NULL

if ----- is NULL in your table

edited Jan 22, 2018 at 7:30

answered Oct 16, 2016 at 11:31

MadddinTribleD

4056 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

YVS1102 Over a year ago

its working thanks. Just need to remove pen (alias)

Kannan Kandasamy · Accepted Answer · 2016-10-16 11:50:34Z

You can try this way...

;WITH cte
AS (SELECT *,
  MAX(CONVERT(int, REPLACE(KodePenilaian, 'PE000', ''))) OVER () AS MaxNum,
  ROW_NUMBER() OVER (ORDER BY kodePenilaian) AS rn
FROM YourTable)
UPDATE cte SET KodePenilaian = concat('PE000', maxnum + rn)
    WHERE KodePenilaian IS NULL

Your table

create table YourTable (
IdPenilaian int, KodePenilaian varchar(20),  Nip  int, PositionCode varchar(10), Total INT)

insert into YourTable
(IdPenilaian , KodePenilaian       ,   Nip  , PositionCode , Total) values
   (  1613      ,     NULL         ,      1603405   ,'P028',          0  )
  ,(  1618      ,     NULL         ,      1602999   ,'P028',          0  )
  ,(  1641      ,    'PE0001568'   ,       603060   ,'P040',         35  )
  ,(  1640      ,    'PE0001567'   ,      1411862   ,'P007',         35  )

Collectives™ on Stack Overflow

Update multiple rows SQL Server 2008

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related