Help changing update command to trigger - Source

You are viewing an old version of this question. View the current version here

Revision: 97292
User: Shawn Logan
Date: 2020-07-01 14:25

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;

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       |
+--------+-----------+------+-------+---------------------+-----------------+------------------+

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Help changing update command to trigger - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB