Forum Stats

  • 3,769,299 Users
  • 2,252,946 Discussions
  • 7,874,982 Comments

Discussions

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

User_7DS20
User_7DS20 Member Posts: 10 Green Ribbon

Suppose I have a table t1 having same id with following data

ID | Name | Applciation_id | Location

1 | UAT | 123 | USA

1 | TEST | 222 | UK

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

ID | Name | Applciation_id | Location

1 | DEV | 1 23 | USA

1 | TEST | 222 | UK

Best Answer

  • mathguy
    mathguy Member Posts: 10,155 Blue Diamond
    edited Oct 10, 2021 1:19PM Accepted Answer

    If I understand correctly, you want to change name='UAT' to name='DEV' for ID = 1. Correct?

    This is quite easy - the UPDATE statement should "say" exactly what we said in words, above. What did you try?

    update t1
    set    name = 'DEV'
    where  id = 1 and name = 'UAT'
    ;
    

    If you need to do this often, with different values for id and name, it is better to write it with bind variables (which are assigned values at runtime - depending on "user choices"):

    update t1
    set    name = :new_name
    where  id = :id and name = :old_name
    ;
    


Answers

  • jleg
    jleg Member Posts: 1 Blue Ribbon

    If the column ID is a real ID (i mean a unique identifier), you should have a different ID for each row in your table. Thus this ID would be the key to identify a row in your table.

    In your case, if the two rows have the same ID (i think it's a problem), you need to choose another column (one or more) to identify the row you want to update.

    For instance, if you are sure that the name UAT is unique in your table, you can use:

    update t1 set name='DEV' where name='UAT';

    But if there are other rows with the name UAT in the table, they will be updated too. If you don't need that, you have to filter the query and add more columns in the where clause.

    e-g: update t1 set name='DEV' where name='UAT' and Application_id=123;

    Hope this help 😉

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond
    edited Oct 10, 2021 10:09AM

    Hi, @User_7DS20

    Now I want to only update name column of the 1st row only

    There is no built-in order to the rows in a table, so whenever someone talks about "the first row" it's just a short way of saying "the first row WHEN SORTED BY ..." where the "when sorted by ..." part is understood. In this case, what is "the first row"? Do you mean the first row when sorted by application_id? What if there is a tie, that is, two or more rows have the same "when sorted by" value? The title you chose for this thread is "How to Update a table having same id but different data in rows?" Does that mean when there is only one row with a given id (so that row is necessarily "the first row") you don't want to UPDATE it? If so, why didn[t you include an example of that?

    Depending on your answers to these questions, you may want something like this:

    UPDATE  a_table
    SET	name = 'DEV'
    WHERE	ROWID IN (
    	    	   SELECT  MIN (ROWID) KEEP (DENSE_RANK FIRST ORDER BY application_id)
    		   FROM   a_table
    		   GROUP BY id
    		   HAVING	COUNT (*) > 1
    	    	 )
    ;
    

    If you'd care to post CREATE TABLE and INSERT statements for a little sample data (including an id with a tie for first row, and another id with only one row) then I could test it.

  • User_7DS20
    User_7DS20 Member Posts: 10 Green Ribbon

    Hi @Frank Kulash


    Thanks for the update


    Its like one project can have many environments so here id=project(for instance Amazon is a project and this project have different applications under it like amazon prime etc )

    So the example that i mentioned is like Amazon have uat env and Amazon also have dev env. So when a user changes the env to test in the frontend then in the backend it should update the table with the value changed by the user.

  • mathguy
    mathguy Member Posts: 10,155 Blue Diamond
    edited Oct 10, 2021 1:19PM Accepted Answer

    If I understand correctly, you want to change name='UAT' to name='DEV' for ID = 1. Correct?

    This is quite easy - the UPDATE statement should "say" exactly what we said in words, above. What did you try?

    update t1
    set    name = 'DEV'
    where  id = 1 and name = 'UAT'
    ;
    

    If you need to do this often, with different values for id and name, it is better to write it with bind variables (which are assigned values at runtime - depending on "user choices"):

    update t1
    set    name = :new_name
    where  id = :id and name = :old_name
    ;