gonz Posted September 1, 2015 Share Posted September 1, 2015 Hi all. I have set up a couple of tables in my equipment control database. One table has one record per piece of equipment, the other table has the date and location of where the tool is (was) used. I need to update the field in the equipment table that shows where the equipment IS (lastloc) with the move to field (movto) from the location table. There is a one to many relationship between the location table and the equipment table, that way I can keep the use history of the equipment, so I need to pick out the last entry for that equipment (movdate) would have to be used for ordering the data subset. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted September 1, 2015 Share Posted September 1, 2015 Simply query the location table for the item id and the max date Quote Link to comment Share on other sites More sharing options...
gonz Posted September 1, 2015 Author Share Posted September 1, 2015 I am new to PHP, but this is what I wrote: update equip set equip.eqlkl = select movto from eqmove where equip.eqnum=eqmove.eqp and eqmove.movdate=max(eqmove.movdate)order by desc eqmove.id limit 1; I am here in this forum because it is not working. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 1, 2015 Share Posted September 1, 2015 there's no need to do an update query at all. you don't need to store the location in your equipment table, because you know the location from the location table. you are just duplicating data that you can easily find by running a query that JOINs the two tables. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 1, 2015 Share Posted September 1, 2015 A principle of database design is do not store derived data, or the same data in more than one location. You can find an items location by querying the eqmove table, so why store it again in the other table? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 1, 2015 Share Posted September 1, 2015 Snap! Quote Link to comment Share on other sites More sharing options...
gonz Posted September 1, 2015 Author Share Posted September 1, 2015 I understand the no duplicating data concept, but it makes it so much easier to show only one table to the users with that last location - much like an invoice total in the header record is not really necessary as you can always recalculate it!! Quote Link to comment Share on other sites More sharing options...
Barand Posted September 1, 2015 Share Posted September 1, 2015 You store data for efficiency, not for their appearance to the user. You are confusing relational database tables with spreadsheets. You have your code in between the db and the user, so you can present it any way you want, totally independent of the way it is stored. Use relational joins to get data from several tables with a single query. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.