SQLTeam.com | Weblogs | Forums

Help with update statement


#1

Dears

i need to know how to update table with list of values as per below picture:


#2

Not sure if this is what you want:

update yourtable
   set [index]=id
 where id in (1,2,3,4,5)
;

#3

thank you bitsmed, but the condition is not correct, i need to update the Index column in the table with range of values (for example i will read the values from another table), here is another example:


#4

Is this what you want?

UPDATE o SET 
	o.Flag = o.ID
FROM
	dbo.Orders o
	JOIN dbo.Flag f
		ON o.ID = f.Flag_ID;

#5

To get the output you requested, you could do:

with cte_order
  as (select id
            ,row_number() over(order by id) as rn
        from [order]
     )
    ,cte_flag
  as (select flag_id
            ,row_number() over(order by (select null)) as rn
        from flag
     )
update a
   set a.flag=c.flag_id
  from [order] as a
       inner join cte_order as b
               on b.id=a.id
       inner join cte_flag as c
               on c.rn=b.rn
;

Are any other columns in flag table? Columns to link/join to order table? Or perhaps columns to sort? If not, please describe, how should we know, which flag belongs to which item?


#6

Because the Flag Table has nothing that stipulates the order of the flags as they appear in your graphic, any and all attempts to accomplish your goal are doomed to eventual failure because so-called "natural" or "order of entry" sorts are not reliable at all. You may get code that works for now but eventually the code will silently fail to perform correctly and you'll be in a real world of hurt.


#7

No, i need the values from table 2 Flags to be inserted into table 1 orders


#8

Dear bitsmed,

there is not link between the 2 tables, Table orders updated from flags table.

Just inserting all values of Flags table into Orders table.


#9

Thanks for clarification.


#10

So either of these results are valid?

id  item     flag
1   Sugar    1
2   Biscuit  2
3   Pizza    4
4   Apple    6
5   Rice     0
6   Bread    0
id  item     flag
1   Sugar    2
2   Biscuit  4
3   Pizza    1
4   Apple    6
5   Rice     0
6   Bread    0
id  item     flag
1   Sugar    1
2   Biscuit  2
3   Pizza    6
4   Apple    4
5   Rice     0
6   Bread    0

If so, go with solution from @Jason_A_Long