Update table with select results in MYSQL

Question

I am currently doing some exercises to better understand how to use MYSQL. I am trying to update a table that from the select results from two other tables. The general idea is to have the table exp_in_tamiahua being updated from the information on the query and keep a comparison of the items purchased vs the max amount of items allowed. rqsd as a foreign key that gets information from the purchase order table (rqsm) and the exp_in_tamiahua table has no connection to them

Update
    exp_in_tamiahua
Set
    exp_in_tamiahua.cantidad = Total,
    exp_in_tamiahua.ultima_actualizacion = rqsm.date
Select
  rqsd.ref,
  rqsd.det_item,
  Sum(rqsd.det_qty) As Total,
  rqsm.date
From
  rqsd Inner Join
  rqsm
    On rqsd.reqid = rqsm.reqid,
Where
  rqsd.det_item In ('ABRA-114', 'SODEXO GC') And
  rqsm.date > '2017 / 1 / 1'
Group By
  rqsd.det_item, rqsm.date

This is my first semester working with databases so I do apologize for any horrible noob mistake but I would also appreciate it if you could point them out :). Would I be better off using the Insert function?

Insert is used when you have an entirely new record you are adding, and Update is what is used when you already have a record and you want to change it. — rasmeister, Mar 09 '17 at 22:37
Look here for some examples http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query — Ilion, Mar 09 '17 at 22:38

Update table with select results in MYSQL

0 Answers0