MySQL Forums
Forum List  »  Views

Re: Help with query strategy for view on table
Posted by: Brian Dieckman
Date: January 17, 2019 09:18AM

For anyone who runs across this question in the future, here's how I solved the issue:

1) new table containing the fields I wanted in the view.
2) a trigger on the old table that inserts/updates in the new table.

Here's the trigger

IF NEW.state = 1 THEN
/* "on" state: create new row */
INSERT INTO EventsC(host,type,start)
VALUES (NEW.host,NEW.type,NEW.timestamp);
ELSEIF NEW.state = 0 THEN
/* "off" state: find and update existing row */
UPDATE EventsC SET end = NEW.timestamp
WHERE id = (SELECT a.id
FROM (SELECT MAX(id) AS id
FROM EventsC
WHERE type = NEW.type
AND end IS NULL) AS a);
END IF

The only weird thing is the double-wrap of the subquery that selects the "1" row for the corresponding "0" event. MySQL gave me a 1093 error that basically said I can't use a table in the target of an update statement in a later subquery.

I tried an inner join to the same table but couldn't get the logic right so I just obfuscated the subquery a layer deeper and the query optimizer didn't spot it. Now that I think about it, there's probably an optimizer switch to turn that off.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Help with query strategy for view on table
263
January 17, 2019 09:18AM


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.