Re: No Subject....

There are three basic ways I remember seeing this situation handled: 1. First try to SELECT the product's forecast you are to insert or update. If you get it back, then you know you have to UPDATE, if not, you have to INSERT. This is less efficient than #2 because you are always sending two queries to the RDBMS. 2. Just go ahead and run an UPDATE for everything. But if you get an error back, then send an INSERT because the UPDATE didn't actually work. This is the more popular way I've seen, and it's more efficient than #1 because you can decide which to send first (if you have a properly designed schema, it doesn't matter if you try the UPDATE before the INSERT or the INSERT before the UPDATE, but you should wisely choose the order to reduce the number of times you send two queries). 3. Lots of modern front ends specifically distinguish between the operations to be performed. That's why you see an "Add" or "New" button as well as an "Edit" button. If the user clicks the "Add" or "New" button, the operation to be performed is an INSERT. If they clicked the "Edit" button, then it's an UPDATE. This is the most efficient and cleanest way of deciding between UPDATE vs INSERT, but it may be more work for your users. Doug At 01:30 PM 11/22/00, Fábio Ottolini wrote: >Hej All! > >Which is the best way to discover if a entry on a MySQL table must be updated or created? >Example: >Suppose I have a table called products and on this table I am going to save information about product's forecast using fields 'name', 'mont_year' and 'quantity'. In some situations it will be necessary to update fields (client has decided to change quantities for a specific product and month already defined on the forecast) and in others it will be necessary to add a new entry (client added a new month or product on the forecast - the period covered with this forecast tool advances in time according to the month). If I use update for every variable of this form (the main interface is obviously a form) then what didn't exist before will continue not to exist and probabily I'll receive some error messages from MySQL regarding the update of fields that don't match the specified criteria. If I use insert I'll have double or even triple entries for certain products on the same month, but by the same time I'll be able to include the new ones. >Do you have some ideas about the best approach for this problem or it's just matter of making comparisions between queries? > >Best regards, > >Fábio Ottolini >