Jump to content

Update table


gonz

Recommended Posts

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.

Link to comment
Share on other sites

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.
 

Link to comment
Share on other sites

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!!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.