3

I want to update records in a table. These records I want to update should be in order (first by pkey asc). Next the qty I want to update will be stored in another field.

update table

pkey description status tray
---- ----------- ------ ----
1    widget      0      0
2    widget      1      0
3    widget      1      0
4    widget      1      0
5    widget      2      0
6    widget      2      0

widget table

desc   QtyPerCase
------ ----------
widget 2

For this example I want to update the first (qtypercase) to tray XX order by pkey asc.
There will be different widgets and qtypercase so I need it to be dynamic.

Expected result:

pkey description status tray
---- ----------- ------ ----
1    widget      0      0
2    widget      1      99
3    widget      1      99
4    widget      1      0
5    widget      2      0
6    widget      2      0

I hope I asked this well. Thanks guys in advance for the help.

LukStorms
  • 28,916
  • 5
  • 31
  • 45
PSikorski
  • 31
  • 2
  • How your end result should look alike? – Ankit Bajpai Dec 21 '19 at 15:21
  • Sybase is the DB. I want to update tray to 99 so end result would be pkey description status tray ---- ----------- ------ ---- 1 widget 0 0 2 widget 1 99 3 widget 1 99 4 widget 1 0 5 widget 2 0 6 widget 2 0 – PSikorski Dec 21 '19 at 16:50
  • Sybase is the DB. I want to update the tray column to 99 so end result would be pkey records of 2 and 3 would have tray column to 99. – PSikorski Dec 21 '19 at 16:57
  • I didnt explain this very well. Maybe a better way to explain it is to say I want to update a qty of records and that qty of record to update is in another table. I hope this makes more sense. – PSikorski Dec 22 '19 at 20:31
  • @PSikorski I don't understand why "1 widget 0 0" didn't get an upate? Do you specify the status too? In my opinion the easiest way to solve this, is to run a cursor over #widget and using a while condition inside of it (`while @c1_ QtyPerCase > 0`). Then update the row with the lowest `pkey` and not having `tray`= 99. – csaar Dec 31 '19 at 08:59

0 Answers0