update “tblMembers” as t1
set t1.ACTIVE = false
where t1.FIRSTNAME,t1.SURNAME = (select FN,SN from Table1)
It was years ago when I worked on Oracle DBs but I was sure you could do this. Seems not to like that in BASE
If not on BASE how?
Don’t know if this would work in Oracle, but won’t work in MariaDB, PostgreSQL, Firebird and HSQLDB.
Depending on the database you have to use different code. In both internal DBs you could use
update "tblMembers"
set "ACTIVE" = false
where "FIRSTNAME"||"SURNAME" IN (select "FN"||"SN" from "Table1")
Haven’t tested before. If you use = and then a subselect, the subselect has to contain only one row. So I would test with IN.
Concatening fields will work in internal databases with ||. For other databases you will need CONCAT("FIRSTNAME","SURNAME")
1 Like
Oh yes of course IN is the key here as subselect = can only contain 1 or zero rows unless you have cursor loops and stuff like that.
Forgotten so much SQL in retirement of 6 years from Oracle systems engineering.
Thanks for the pointers people
GT
1 Like
In addition to @RobertG 's answer.
We can use “tuples” in Oracle in the following context:
select null from dual where (1, 1) = (select 1, 1 from dual)
The query returns null.
If we replace dual in the subquery with the name of a table that contains more than one record, an error will occur.
The same applies to PostgreSQL.
Depending on the backend (underlying database service which Base is connected to) you may need to quote field names and literal values. One common convention is to use double quotes for identifiers and single quotes for literal values.
Try:
update "tblMembers" as "t1"
set "t1"."ACTIVE" = false
where "t1"."FIRSTNAME","t1"."SURNAME" = (select "FN","SN" from "Table1")
… or perhaps also quoting the boolean literal (whether boolean true/false is considered reserved words, identifiers or literals seems to be handled differently depending on platform).
update "tblMembers" as "t1"
set "t1"."ACTIVE" = 'false'
where "t1"."FIRSTNAME","t1"."SURNAME" = (select "FN","SN" from "Table1")
Note that the “embedded” database service in Base also runs as a backend service of sorts, so this is (or may be) relevant regardless of whether you are accessing a local or remote database.
Seems that I jumped the gun. IN is most likely the correct operator for you, as @RobertG commented. Multiple field compare should work though, and is more explicit (and often also more efficient) than concatenation to single value, but you may need bracketing (parentheses)
update "tblMembers" as "t1"
set "t1"."ACTIVE" = false
where ("t1"."FIRSTNAME","t1"."SURNAME") in (select "FN","SN" from "Table1")
Experiment around our suggested modifications of your query. (Make sure you have a test base when building queries. Do not experiment on a production base.) Consider also whether Splitting the condition of the WHERE clause, using AND, is a possible approach for you.