New in the Community? Get started here

Schneider Electric Exchange Community

Discuss and solve problems in energy management and automation. Join conversations and share insights on products and solutions. Co-innovate and collaborate with a global network of peers.

Register Now
Geo SCADA Expert Forum
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Lt. Commander

[import] UPDATE query with table join

>>Message imported from previous forum - Category:ClearSCADA Software<<
User: nminchin, originally posted: 2018-12-04 05:51:57 Id:331
Is it possible to perform an UPDATE query that includes a table join?

E.g. In SQL Server this would be:

`UPDATE arl
SET arl.Delay1 = 1200
FROM CAlarmRedirectionList arl
INNER JOIN CDBPoint pnt on arl.id = pnt.id
WHERE pnt.FullName = 'Some point fullname'`


Reply User: sbeadle, posted: 2018-12-04 08:36:14
Use a nested SELECT:
update table set field etc where table.something = (select ...)
(The select should return a single row/column)


Reply User: BevanWeiss, posted: 2019-01-03 22:06:53
or replace the = with IN and then the SELECT can return a set... :smiley: simples


Reply User: du5tin, posted: 2019-01-04 02:33:42
Nick, have you tried setting the aggregate field using the - notation? I am not sure if that works.

 


Reply User: BevanWeiss, posted: 2019-01-11 03:19:02
I'm pretty sure it doesn't... I think I've tried this before, from memory the indirection was only supported in SELECT statements.