[Imported] UPDATE query with table join

[sbeadle]

>>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'`

[sbeadle · Accepted solution]

>>Responses imported from previous forum

Reply From 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 From 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 From 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 From 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.