Jadvaldagi SQLda ikki ustunning yig'indisi

My assignment is that i have Three columns Stock_Opening,Stock_New,Stock_Available apart from P_Key and F_key.I have to insert some value in Stock_New. Then Stock_Opening + Stock_New will add up and value will go into Stock_Available

Muammo: Men Stock_New da qiymatni kiritganimda va uni faqat Stock_New agar men yana bir so'rovni bajarayotgan bo'lsam, hozirda har ikkala jadvalni ham Stock_New yangilang  qiymati va oldingi qiymati bilan Stock_Available . Men bir vaqtning o'zida Stock_New va Stock_Available yangilashini xohlayman Meni ijro etadigan so'rov

UPDATE Stock
SET Stock_New = Stock_New + '2',
    Stock_Available = Stock_Opening + Stock_New
WHERE Med_ID = 'PANAD223'

Displeyni tekis qilib oling

This is the table before execution

This is the table after first execution

This is the table after second execution

0
Displey rasmlarni o'rniga kodingizni text deb yozing.
qo'shib qo'ydi muallif Larnu, manba

7 javoblar

Ko'rib turganimdek, hisoblangan ustunlar sizning holatlaringizga yaxshi mos keladi.

Hisoblangan ustunlar aslida jismoniy bo'lmagan virtual ustunlardir   ustun PERSISTED belgilanmagan bo'lsa, jadvalda saqlanadi. Qiymatlari uchun   Hisoblangan ustunlar, har bir havola qilinayotgan vaqtda qayta hisoblanadi   so'rovlar

see https://www.c-sharpcorner.com/article/computed-columns-in-sql-server/

1
qo'shib qo'ydi

Stock_New is updated with latest value after update is finished. Try:

UPDATE Stock SET Stock_New = Stock_New + 2 , Stock_Available = Stock_Opening + Stock_New+2 WHERE Med_ID='PANAD223'
0
qo'shib qo'ydi

Agar UPDATE dan biror ustunga murojaat qilsangiz, UPDATE dan qiymatini olasiz. Ya'ni, Stock_New har doim Stock_Available = Stock_Opening + Stock_New da ikkita qisqa. Bu erda 2 ni qo'shishingiz kerak.

UPDATE stock
       SET stock_new = stock_new + 2,
           stock_available = stock_opening + stock_new + 2
       WHERE med_id = 'PANAD223';

Bundan tashqari, agar siz sonli harflaringiz bo'lsa, ularni bitta qo'shtirnoqlarga qo'shmang. Bu satrlar uchun.

0
qo'shib qo'ydi
create proc sampleupdate
as
begin
declare @stocknew int,
declare @stockopening int 

set @stocknew=(select Stock_New from Stock (condition))
set @stockopening=(select Stock_Opening  from Stock (condition))

set @[email protected] +'2'
UPDATE Stock SET Stock_New [email protected], Stock_Available = @stockopening + @sn WHERE 
Med_ID='PANAD223'
end
0
qo'shib qo'ydi
saqlanadigan protsedura uchun borishning eng yaxshisi, ma'lumotlar turi o'zgarishi mumkin
qo'shib qo'ydi muallif Anjan, manba

Quyidagi kabi yangilashni iltimos qilamiz:

UPDATE Stock SET Stock_New = Stock_New + '2', Stock_Available = Stock_Opening + Stock_New + '2' WHERE Med_ID='PANAD223'
0
qo'shib qo'ydi

SQL stock_available = stock_opening + stock_new + 2 bo'lishi mumkin, chunki SQL yangilangan emas, balki birinchi qiymatni hisoblaydi.

Boshqa ikki ustundan hisoblangan Stock_Available ustunini yaratish osonroqdir.

ALTER TABLE Stock
DROP COLUMN Stock_Available

ALTER TABLE Stock
ADD Stock_Available AS Stock_New + Stock_Opening

Endi siz faqat bitta yangilashni amalga oshirishingiz kerak:

UPDATE Stock Set Stock_New = Stock_New + 2 WHERE Med_ID='PANAD223'

va Stock_Available avtomatik yangilanadi

0
qo'shib qo'ydi

ma'lumotlar yangilanishini 1, Stock_New = 28, Stock_Opening = 20

UPDATE Stock SET Stock_New = 28 + '2', 
                 Stock_Available = 20 + 28
 WHERE Med_ID='PANAD223'

Siz ma'lumotlar yangilanishini 2, Stock_New = 30, Stock_Opening = 20

UPDATE Stock SET Stock_New = 28 + '2', 
                 Stock_Available = 20 + 30
 WHERE Med_ID='PANAD223'

Shuning uchun (+ 2 ') qo'shasiz

UPDATE Stock SET Stock_New = Stock_New + '2', 
                 Stock_Available = Stock_Opening + Stock_New + '2'
 WHERE Med_ID='PANAD223'
0
qo'shib qo'ydi