MySQL Forums
Forum List  »  Triggers

Issues with converting update command to trigger
Posted by: Shawn Logan
Date: July 01, 2020 09:29AM

Current command:
[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.

table 1 id_name_update
[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 | |
+-------------+----------+------+-----+---------+-------+


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

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

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.