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.