Help changing update command to trigger (answered)
Current command: MariaDB [sensor_logs]> UPDATE telemetry_log JOIN id_name_update ON telemetry_log.device_id = id_name_update.device_id SET telemetry_log.device_desc = id_name_update.device_desc WHERE telemetry_log.device_desc IS NULL;(this works)
I would like to create a trigger to do this automatically. I have tried 100's of possible triggers configurations, all of them ending with a error either 1064 sql syntax or 1442 same table. Thank you in advance for any direction you all can give me.
table 1 id_name_update
MariaDB [sensor_logs]> show columns in id_name_update;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| device_id | char(50) | NO | PRI | NULL | |
| device_desc | char(50) | YES | NULL | ||
| hum_cal | int(11) | NO | 0 | ||
| temp_cal | int(11) | NO | 0 |
+-------------+----------+------+-----+---------+-------+
MariaDB [sensor_logs]> select * from id_name_update;
+-----------+------------------+---------+----------+
| device_id | device_desc | hum_cal | temp_cal |
+-----------+------------------+---------+----------+
| 11 | PaaRoomByFreezer | 0 | 0 |
| 13 | Compressor | 0 | 0 |
| 15 | PaaLab | 0 | 0 |
| 16 | PottingArea | 0 | 0 |
| 30 | BondFront | 0 | 0 |
| 60 | RightFreezerAir | 0 | 0 |
| 62 | LeftFreezerAir | 0 | 0 |
| 71 | PaaNewRoom | 0 | 0 |
| 72 | BondBack | 0 | 0 |
| 73 | PrimerBooth | 0 | 0 |
+-----------+------------------+---------+----------+
Table 2 MariaDB [sensor_logs]> show columns in telemetry_log;
+-----------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+----------------+
| rec_id | int(11) | NO | PRI | NULL | auto_increment |
| device_id | char(50) | YES | NULL | ||
| type | int(11) | YES | NULL | ||
| value | char(50) | YES | NULL | ||
| date | datetime | YES | MUL | NULL | |
| unit_of_measure | char(1) | YES | NULL | ||
| device_desc | char(50) | YES | NULL |
+-----------------+----------+------+-----+---------+----------------+
MariaDB [sensor_logs]> SELECT * FROM telemetry_log ORDER BY date DESC Limit 10;
+--------+-----------+------+-------+---------------------+-----------------+------------------+
| rec_id | device_id | type | value | date | unit_of_measure | device_desc |
+--------+-----------+------+-------+---------------------+-----------------+------------------+
| 38548 | 72 | 3 | 70.2 | 2020-07-01 10:00:11 | F | NULL |
| 38547 | 72 | 5 | 52.77 | 2020-07-01 10:00:11 | % | NULL |
| 38546 | 72 | 5 | 52.77 | 2020-07-01 09:59:45 | % | BondBack |
| 38545 | 13 | 6 | 3.00 | 2020-07-01 09:58:52 | V | Compressor |
| 38544 | 13 | 3 | 88.36 | 2020-07-01 09:58:52 | F | Compressor |
| 38543 | 11 | 3 | 71.02 | 2020-07-01 09:57:41 | F | PaaRoomByFreezer |
| 38542 | 11 | 5 | 58.63 | 2020-07-01 09:57:41 | % | PaaRoomByFreezer |
| 38541 | 30 | 3 | 70.54 | 2020-07-01 09:57:41 | F | BondFront |
| 38540 | 30 | 5 | 52.50 | 2020-07-01 09:57:41 | % | BondFront |
| 38539 | 71 | 3 | 72.5 | 2020-07-01 09:57:41 | F | PaaNewRoom |
+--------+-----------+------+-------+---------------------+-----------------+------------------+
It may have been as simple as not using phpmyadmin... when i entered the command at the command line : MariaDB [sensor_logs]> CREATE TRIGGER add_desc BEFORE insert on telemetry_log FOR EACH ROW set new.device_desc = ( IT WORKED perfectly.. Thank you for all the help.
