Skip navigation links

MySQL Forums :: Newbie :: inserting + sorting


Advanced Search

Re: inserting + sorting
Posted by: irek kordirko ()
Date: March 29, 2012 04:10PM

Mateus Almeida da Silva Wrote:
-------------------------------------------------------
>
> -> 1st part:

This query will update records that exists in the testing table
with corresponding data (client's ids) from the sell_registry table:
UPDATE testing
JOIN sell_registry ON testing.client_code = sell_registry.client_code
SET testing.sell_date = sell_registry.sell_date;

The above will only update existing records in the testing table,
but wont't insert new records into it in case there is no some client's codes in the testing table.

If you want to copy all clients from one table to another one, inserting clients that don't exist in the testing table, and updating clients that exist in it,
this query will do that work (assuming there is an unique index on client_code created in the testing table):

INSERT INTO testing( client_code, sell_date ) 
SELECT client_code, sell_date FROM sell_registry
ON DUPLICATE KEU UPDATE testing.sell_date = sell_registry.sell_date

When there is no such unique index, this query won't work.
In this case you need to run two separate commands:

UPDATE testing
JOIN sell_registry ON testing.client_code = sell_registry.client_code
SET testing.sell_date = sell_registry.sell_date;

INSERT INTO testing( client_code, sell_date ) 
SELECT client_code, sell_date FROM sell_registry
WHERE NOT EXISTS(
   SELECT 1 FROM testing 
   WHERE testing.client_code = sell_registry.client_code
);

>
> -> second part:
> I need to do a calculus with two dates. I need to
> subtract: "actual date" - "sell_date" (both are
> "datetime" values). Problem: the result needs be
> in "months", in an integer value. What should I
> do?

What exactly do you mean "difference in months" ?
What about rounding ?
Could you tell us for example how many months would be:
- "2012-01-02" minus "2012-01-01" - should be 0 or 1 month ?
- "2012-01-15" minus "2012-01-01" - should be 0 or 1 ?
- "2012-01-16" minus "2012-01-01" - should be 0 or 1 ?
- "2012-01-30" minus "2012-01-01" - should be 0 or 1 ?
- "2012-03-31" minus "2012-01-01" - should be 2 or 3 months ?
etc.

Options: ReplyQuote


Subject Written By Posted
inserting + sorting Mateus Almeida da Silva 03/29/2012 09:46AM
Re: inserting + sorting laptop alias 03/29/2012 04:08PM
Re: inserting + sorting Mateus Almeida da Silva 03/30/2012 06:08AM
Re: inserting + sorting irek kordirko 03/29/2012 04:10PM
Re: inserting + sorting Mateus Almeida da Silva 03/30/2012 05:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.