Skip to content
Advertisement

update one row where it has multiple rows

I have below example table (ID is the primary):

ORGTable:

id  |  Type   |  Info
----+---------+-------
1      type1     info1
1      type2     info2
1      type3     info3
2      type4     info4
2      type5     info5
3      type6     info6
3      type7     info7
4      type8     info8
5      type9     info9

and below query would return data for ID 1 (ID is the primary), ex.

select * from ORGTable where ID ='1'

Results of the query

    id  |  Type   |  Info
    ----+---------+-------
    1      type1     info1
    1      type2     info2
    1      type3     info3

my issue is i would like to update only one row from the above results, for example row 1 to be like below:

Results after update

        id  |  Type   |  Info
        ----+---------+-------
        1      type99    info99
        1      type2     info2
        1      type3     info3

Advertisement

Answer

i have bulk of data, would like to update them in one query where Type=’type1′ and Info=’info1′ then update

This will group all data according to your condition, then update them

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
   FROM ORGTable WHERE type='type1' AND Info='info1'
)
UPDATE cte SET type = 'type99',info = 'info99' WHERE rn = 1

If you just want to update one value:

    update
    TOP (1) ORGTable
set
    type = 'type99',
    info = 'info99'
where
    id = '1'
    and type = 'type1'
    and Info = 'info1'

db<>fiddle

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement