CODEWITHSUNDEEP
odbcopenedgeprogress-db
Leluloota
Leluloota

Reputation: 11

OpenEdge ODBC UPDATE for multi-tenant database

When using ODBC to OpenEdge multitenant database I can make query and get expected result. SELECT "LogiVal" FROM PUB."SysCtrl" AS mtc WHERE tenantName_tbl (mtc) = ('tenantID') AND "ParamId" = 'AccoGrpProd' This will return only one column / row as expected.

I can easily update this field to all tenants, but how to limit it to specific tenant?

I'm using DBeaver while testing query syntax and thought the syntax would be similar as in SELECT but no luck. This is what I tried. Let's say I like to set value to field "LogiVal" = "1".

UPDATE PUB."SysCtrl" AS mtc SET LogiVal = '1' WHERE tenantName_tbl (mtc) = ('tenantID') AND "ParamId" = 'AccoGrpProd'

Upvotes: 0

Views: 40

Answers (2)

Leluloota
Leluloota

Reputation: 11

I found the syntax to update value in OpenEdge multi-tenant database via ODBC. UPDATE <tableName> SET <fieldName> = '<newValue>' WHERE <condition1> AND <condition2> AND tenantName_tbl (<tableName> ) = '<tenantId>'

Upvotes: 0

Tom Bascom
Tom Bascom

Reputation: 14020

This OpenEdge kbase has an example of a MT SQL Query that seems to illustrate choosing a subset of tenants:

SELECT COUNT(*) FROM DBNAME.PUB.Table1
INNER JOIN DBNAME.PUB.Table2 ON
Table2.columnA = Table1.columnA
AND tenantId_tbl(Table1) = tenantId_tbl(Table2)
LEFT JOIN DBNAME.PUB.Table3 ON
Table3.columnB = Table2.columnB
AND Table3.columnC = Table2.columnC
AND tenantId_tbl(Table1) = tenantId_tbl(Table3)
INNER JOIN DBNAME.PUB.Table4 ON
Table4.columnB = Table2.columnB
AND tenantId_tbl(Table1) = tenantId_tbl(Table4)
WHERE
Table1.columnD >= '1/1/2018'
AND tenantId_tbl(Table1) IN (1,5)

The AND tenantId_tbl(Table1) IN (1,5) portion is, I think, what you might be looking for.

There additional examples of MT SQL queries in the SQL documenation. For instance, this is at the bottom of the page linked above:

SELECT * FROM pub."_Tenant" AS t
INNER JOIN pub.mtorder AS o
ON t."_Tenant" = tenantId_tbl (o)
INNER JOIN pub.mtorderline AS ol
ON c.custnum = o.custnum
AND tenantId_tbl (o) = tenantId_tbl (ol)
;

Upvotes: 1

Related Questions