phpmyadmin Question posted in
The official documentation can be found here.

SQL Update record when field is in the past – Phpmyadmin

how can I set all records in the field "published"=0 whose date in field F12 is older than today?

Unfortunately, the field F12 is not a DateTime field, but a string. Unfortunately, I can’t change that. I have to convert the string into a date first.

Unfortunately, this code does not work

UPDATE 'mytable' 
SET 'published' = '0' 
WHERE STR_TO_DATE('F12', '%d.%m.%Y') DATEDIFF(day, 'F12', NOW()) >= 1

A screenshot of phpMyAdmin is here:

enter image description here

Thank you very much for your support and best regards

Tags:

2

Answers


  1. 0

    I would expect logic like this:

    update mytable
    set published = 0
    where STR_TO_DATE(F12, '%d.%m.%Y') < curdate();

    Note that single quotes are being used only for the string constant. That could be the issue with your query. Or it could be that you are using now() which has a time component so it also updates values whose date is today.

    Login or Signup to reply.
  2. 0

    This is not the Answer. its only a Sample for virtual fields:

    create a sample table

    CREATE TABLE `table1` (
  `a` int(11) NOT NULL,
  `F12` varchar(32) DEFAULT NULL,
  `F12new` date AS (STR_TO_DATE(`F12`, '%d.%m.%Y')) PERSISTENT,
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

    sample

    MariaDB [bernd]> CREATE TABLE `table1` (   `a` int(11) NOT NULL,   `F12` varchar(32) DEFAULT NULL,   `F12new` date AS (STR_TO_DATE(`F12`, '%d.%m.%Y')) PERSISTENT ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.19 sec)

MariaDB [bernd]> insert into table1 (a,F12) VALUES (1,'1.2.2018');
Query OK, 1 row affected (0.02 sec)

MariaDB [bernd]> select * from table1;
+---+----------+------------+
| a | F12      | F12new     |
+---+----------+------------+
| 1 | 1.2.2018 | 2018-02-01 |
+---+----------+------------+
1 row in set (0.01 sec)

MariaDB [bernd]> insert into table1 (a,F12) VALUES (1,'17.05.2029');
Query OK, 1 row affected (0.01 sec)

MariaDB [bernd]> select * from table1;
+---+------------+------------+
| a | F12        | F12new     |
+---+------------+------------+
| 1 | 1.2.2018   | 2018-02-01 |
| 1 | 17.05.2029 | 2029-05-17 |
+---+------------+------------+
2 rows in set (0.00 sec)

MariaDB [bernd]>

    Now you only set a index on F12new

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search