Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Update end date of current record with today's date and and update start_date of new record with today's date

Current record:

enter image description here

When I update the value of current record’s activity_weightage as 2 it should pick up start_date as current_date and end_date as 31/12/2099.

enter image description here

and it should update the original record’s end date with today’s date.

enter image description here

How to write SQL with the same?

UPDATE activity_demo
SET 
    end_date = CURRENT_DATE
WHERE 
    activity_type = 'Digital - Content Syndication'
    AND end_date = '31/12/2099';
INSERT INTO activity_demo (activity_type, activity_weightage, start_date, end_date)
VALUES 
    ('Digital - Content Syndication', 
    2,  -- New activity_weightage
    CURRENT_DATE,  -- Start date is today's date
    '31/12/2099');  -- End date remains '31/12/2099'

I am not able to get it correct by above query.

Tags:

2

Answers


  1. 0

    A model:

    CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name INT, 
  age INT DEFAULT 1,
  start_date DATE DEFAULT (DATE(CURRENT_TIMESTAMP)),
  end_date DATE DEFAULT '2099-12-31'
);
INSERT INTO test VALUES
(DEFAULT, 111, DEFAULT, '1999-01-01', DEFAULT),
(DEFAULT, 222, DEFAULT, '1999-01-01', DEFAULT);
SELECT * FROM test;
    id name age start_date end_date
    1 111 1 1999-01-01 2099-12-31
    2 222 1 1999-01-01 2099-12-31 
    INSERT INTO test
SELECT CASE WHEN temp.id = 0 THEN src.id END, 
       src.name, 
       src.age + temp.id,
       CURRENT_DATE,
       src.end_date
FROM test src
CROSS JOIN (SELECT 0 UNION SELECT 1) temp (id)
WHERE src.name = 111 AND src.end_date = '2099-12-31'
ON DUPLICATE KEY UPDATE
test.start_date = src.start_date,
test.end_date = CURRENT_DATE

    SELECT * FROM test;
    id name age start_date end_date
    1 111 1 1999-01-01 2024-10-11
    2 222 1 1999-01-01 2099-12-31
    3 111 2 2024-10-11 2099-12-31

    fiddle

    Login or Signup to reply.
  2. 0

    You are using a non standard date format but if you wish to continue doing so use date_format function https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-format

    select cURRENT_DATE,date_format(CURRENT_DATE,'%m/%d/%Y')

+--------------+--------------------------------------+
| cURRENT_DATE | date_format(CURRENT_DATE,'%m/%d/%Y') |
+--------------+--------------------------------------+
| 2024-10-11   | 10/11/2024                           |
+--------------+--------------------------------------+
1 row in set (0.000 sec)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search