CODEWITHSUNDEEP
mysql
Nyx Assasin
Nyx Assasin

Reputation: 141

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

Upvotes: 0

Views: 31

Answers (1)

Blank
Blank

Reputation: 12378

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'

Upvotes: 1

Related Questions