Informix:使用SELECT进行更新-语法? [英] Informix: UPDATE with SELECT - syntax?

问题描述

我想为所有活动持续时间太长的人更新我的表.更新应该更正一次，对于随后的行，我需要处理新的结果.所以想到了类似的东西

I wanna update my table for all persons whoes activity lasted toooo long. The update should correct one time and for the subsequent rows I need to deal with new result. So thought about something like

UPDATE summary_table st
SET st.screen_on=newScreenOnValue
    st.active_screen_on=st.active_screen_on-(st.screen_on-newScreenOnValue) --old-value minus thedifference
FROM (
      SUB-SELECT with rowid, newScreenOnValue ... JOIN ... WHERE....
     ) nv
WHERE (st.rowid=nv.rowid)

我知道我可以通过重新运行相同的查询来直接更新第一个和第二个值.但是我的问题是子选择的成本似乎很高，因此想避免两次更新.重复执行同一查询.

I know that I can update the first and the second value directly, by rerunning the same query. But my problem is the costs of the subselect seems quite high and therefore wanna avoid a double-update resp. double-run of the same query.

上面的SELECT只是写我想得到的非正式形式.我知道st不起作用，但是为了更好的理解，我把它留在了这里.当我尝试上面的语句时，我总是在FROM结束的位置返回SyntaxError.

The above SELECT is just a informal way of writting what I think I would like to get. I know that the st doesn't work, but I left it here for better understanding. When I try the above statement I always get back a SyntaxError at the position the FROM ends.

推荐答案

这可以通过以下方式实现:

This can be achieved as follows:

UPDATE summary_table st
SET (st.screen_on, st.active_screen_on) = 
    ((SELECT newScreenOnValue, st.active_screen_on-(st.screen_on-newScreenOnValue)
    FROM ... 
    JOIN... 
    WHERE..))
[WHERE if any additional condition required];

上面的查询在经过尝试和测试的notifyix上运行良好，直到您在FROM，JOIN，WHERE子句中出现任何错误为止.

The above query works perfectly fine on informix tried and tested until you make any errors in the FROM, JOIN, WHERE clauses.

干杯！

这篇关于Informix:使用SELECT进行更新-语法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！