Nyx Assasin Nyx Assasin - 4 months ago 7
SQL Question

MySQL update 1st Table using the 2nd Table with computation

Hello and Good Morning.

I have 2 Tables and it looks like this.

Table: receiving


enter image description here

and
Table: purchorder


enter image description here

and here is my question.

How can I update the column
ReflectedQty
using the column
QtyPack
and
QtyStan
of
purchorder
where column
RInumber
=
RINo
.

Pls take note that the column
QtyStan
of purchorder will / 100

Here is the output I need.

enter image description here

TYSM for future help

JPG JPG
Answer

Try this:

update receiving r
join purchorder p
on r.RInumber = p.RINo
set r.ReflectedQty = r.ReflectedQty - p.QtyStan - (p.QtyStan / 100)
-- where r.RINumber = 'myvalue'

My Edited Code

update receiving r
join purchorder p
on P.RInumber = r.RINo
set p.ReflectedQty = Format(p.ReflectedQty - r.QtyPack - (r.QtyStan / 100),2) where RINumber = 'RI861RMA'