Issues with converting update command to trigger
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 |
+--------+-----------+------+-------+---------------------+-----------------+------------------+
Subject
Views
Written By
Posted
Issues with converting update command to trigger
1159
July 01, 2020 09:29AM
578
July 01, 2020 09:33AM
491
July 01, 2020 02:07PM
515
July 02, 2020 05:55AM
501
July 02, 2020 11:59AM
857
July 02, 2020 07:10PM
552
July 02, 2020 09:45PM
547
July 03, 2020 02:53AM
500
July 03, 2020 07:17AM
516
July 03, 2020 10:43AM
482
July 03, 2020 07:46PM
522
July 15, 2020 05:33AM
525
July 15, 2020 10:33AM
553
July 15, 2020 11:02AM
Sorry, you can't reply to this topic. It has been closed.
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.