Copy old item History/Trends to new item

[markfree]

There was some item changes in my production Zabbix that ended up with a few duplicated items.

I have the old item which has history and trends up to 2021-01-04 when it was disabled.
The new item has data since 2020-12-29 and is enabled.

I wish to maintain only the new item and merge the history and trends from the old item to the new item.

Checking the WEBUI, I was able to find the ItemID from both items.
Old itemid = 107252
New itemid = 136235

So, I thought of copying old items history and trends to the new item but I'm not really sure how to do it safely. And... I'm not very confident with databases.

I saw this MySQL Update suggesting something like this:

UPDATE history SET itemid = <itemid new_host> WHERE itemid = <itemid old_host>;
UPDATE trends SET itemid = <itemid new_host> WHERE itemid = <itemid old_host>;

But it is kind of old.

Since the old item data overlaps with the new item, is that an issue?
Should I disable the new item before updating it?
Updating only "history" and "trends" table is enough?

[markfree]

I created some test items to check the update command above, but It doesn't seem to update items as I expected.

In my test:
Old item - 30886
New Item - 32041

[zabbix]> UPDATE history SET itemid = 32041 WHERE itemid = 30886;
Query OK, 2004 rows affected (0.94 sec)
Rows matched: 2004 Changed: 2004 Warnings: 0

[zabbix]> UPDATE trends SET itemid = 32041 WHERE itemid = 30886;
Query OK, 5105 rows affected (0.23 sec)
Rows matched: 5105 Changed: 5105 Warnings: 0

This update results in actually clearing history/trends from old item, transferring them to the new item.

Maybe a more experienced fellow would point out some thoughts.
Any ideas?

[markfree]

After some digging, I managed to successfully copy history and trends from one item to another.

The sort of syntax I used is this:

INSERT INTO history (`itemid`, `clock`, `value`, `ns`) SELECT '[I][B]<new_itemid>[/B][/I]', `clock`, `value`, `ns` FROM history WHERE itemid = [I][B]<old_itemid>[/B][/I];
INSERT INTO trends (`itemid`, `clock`, `num`, `value_min`, `value_avg`, `value_max`) SELECT '[I][B]<new_itemid>[/B][/I]', `clock`, `num`, `value_min`, `value_avg`, `value_max` FROM trends WHERE itemid = [I][B]<old_itemid>[/B][/I];

The secret was with INSERT INTO and the SELECT clause that added the literal itemid.

Using my example above, as expected, both items share the same history.

MariaDB [zabbix]> INSERT INTO trends (`itemid`, `clock`, `num`, `value_min`, `value_avg`, `value_max`) SELECT '32041', `clock`, `num`, `value_min`, `value_avg`, `value_max` FROM trends WHERE itemid = 30886;
Query OK, 5227 rows affected (0.14 sec)
Records: 5227 Duplicates: 0 Warnings: 0

MariaDB [zabbix]> INSERT INTO history (`itemid`, `clock`, `value`, `ns`) SELECT '32041', `clock`, `value`, `ns` FROM history WHERE itemid = 30886; Query OK, 2008 rows affected (0.10 sec)
Records: 2008 Duplicates: 0 Warnings: 0

New test Item


Old Item


I bet there's a better way, but this is all I could come up with for now.