How to Update a table having same id but different data in rows?-CodePudding

Suppose I have a table t1 having the following data where id value doesn't change

ID	Name	Application_id	Location
1	UAT	123	USA
1	TEST	222	UK

Now I want to only update the name column of the 1st row only(below is the desired o/p)

ID	Name	Application_id	Location
1	DEV	123	USA
1	TEST	222	UK

CodePudding user response：

There is nothing preventing you from using the current value of the column that you are updating. So

update <your_table>
   set name = 'DEV' 
 where id = 1 
   and name = 'UAT';

CodePudding user response：

first row depends on how you sort the data, since the tables represent unordered sets. Presumingly, update your table depending on the ascending sorting of the application_id column such as

MERGE INTO t USING
          (SELECT t.*, 'DEV' AS name_new 
             FROM t
            ORDER BY t.application_id
            FETCH FIRST ROW ONLY) t1
          ON ( t1.id = t.id AND t1.application_id = t.application_id )
WHEN MATCHED THEN UPDATE SET t.name = name_new

Demo