Dears
i need to know how to update table with list of values as per below picture:
Not sure if this is what you want:
update yourtable
set [index]=id
where id in (1,2,3,4,5)
;
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:
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;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?
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.
No, i need the values from table 2 Flags to be inserted into table 1 orders
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.
Thanks for clarification.
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