update in sql

Rohan Gupta

11y1k

i want to update pers_code and pers_name according to sno ..

sno pers_code pers_name

2 P0097871 A.ANNAMALAI

NULL NULL NULL

3 P0098263 A.B.PASARI

NULL NULL NULL

4 P0098275 A.C.SHAH

NULL NULL NULL

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Raja T11y ago
Hi,

http://www.codeproject.com/Tips/442428/How-to-update-a-row-with-another-row-if-column-val
Upendra Pratap Shahi11y ago
http://stackoverflow.com/questions/1345065/sql-query-replace-null-value-in-a-row-with-a-value-from-the-previous-known-value
Rajeesh Menoth11y ago
Hi,

Check my solution Rohan Gupta
Rohan Gupta11y ago
Mr. Raja i think u did not under stand my problem
i have alot of data then i m using multiple update queies accordng to you..

i want one query for update
Raja T11y ago
Hi, try like

UPDATE incidents SET level= '9' WHERE UpdateTime= '2011/01/01 21:00' ;

UPDATE incidents SET level= '24' WHERE UpdateTime = '2011/01/01 21:15' ;

UPDATE incidents SET level= '24' WHERE UpdateTime = '2011/01/01 21:15' ;

UPDATE incidents SET level= '12' WHERE UpdateTime = '2011/01/01 13:50' ;
Rajeesh Menoth11y ago
Hi,

Ref this : SQL QUERY replace NULL value in a row with a value from the previous known value

Rohan Gupta11y ago

Actualy my problem is..

Hello, I have a table of incidents with a level, an ID and an updateTime

 incidentID   Level UpdateTime
 --------------------------------------
  INC001       9 2011/01/01 20:50
  INC001       NULL      2011/01/01 21:00
  INC001       24 2011/01/01 21:13
  INC001       NULL      2011/01/01 21:15
  INC001       NULL      2011/01/01 21:19
  INC001       11 2011/01/01 21:30
  INC001       11 2011/01/01 21:30
  INC002       12 2011/01/01 13:30
  INC002       NULL      2011/01/01 13:50
  INC002       2 2011/01/01 13:55

The goal is to replace the NULL values of the column ‘Level’ by the previous row’s ‘Level ‘( for each ID) :

 incidentID   Level UpdateTime
 --------------------------------------
  INC001        9 2011/01/01 20:50
  INC001        9 2011/01/01 21:00
  INC001        24 2011/01/01 21:13
  INC001        24 2011/01/01 21:15
  INC001        24 2011/01/01 21:19
  INC001        11 2011/01/01 21:30
  INC001        11 2011/01/01 21:30
  INC002        12 2011/01/01 13:30
  INC002        12 2011/01/01 13:50
  INC002        2 2011/01/01 13:55

Upendra Pratap Shahi11y ago
Hello Rohan,

Use below query-

UPDATE table_name SET pers_code = 'your pers code', pers_name = 'your pers name' WHERE sno = 'your sno';
Rajeesh Menoth11y ago
Hi,

Write like this..

UPDATE TableName
SET pers_code = 'P0097871', pers_name = 'A.ANNAMALAI'
WHERE sno = 2;
UPDATE TableName
SET pers_code = 'P0098263', pers_name = 'A.B.PASARI'
WHERE sno = 3;
UPDATE TableName
SET pers_code = 'P0098275', pers_name = 'A.C.SHAH'
WHERE sno = 4;
Raja T11y ago
Hi, Try Manas Mohapatra comments and also explain clearly which scenario you want update i means update pers_code and pers_name according to sno when SNO=Null

UPDATE TableName
SET pers_code = 'your code', pers_name = 'your name'
WHERE sno = NULL ;
Manas Mohapatra11y ago
UPDATE TableName
SET pers_code = 'your code', pers_name = 'your name'
WHERE sno = 2;