Pervasive 8.0, WinXP

I'm trying to figure out the syntax to Update a table, based on a
second query

I want to update UDFKEY_10 in my ORDER MASTER table

I will set UDFKEY_10 = 'Test String'
The where clause joins two tables:
WHERE ("Order Master"."PRTNUM_10" = "Part Master"."PRTNUM_01")

When I run it, I get:
ODBC Error: SQLSTATE = S1000, Native error code = 0
' Invalid row-count in subquery.

Here is the UPDATE SQL (That fails)


UPDATE "Order Master"
SET "Order Master"."UDFKEY_10" = (SELECT 'Test String'
FROM "Order Master","Part Master"
WHERE ("Order Master"."PRTNUM_10" = "Part Master"."PRTNUM_01")
AND ("Part Master"."UDFKEY_01" = 'R' OR "Part Master"."UDFKEY_01" =
'RS')
AND "Order Master"."ORDNUM_10" > '700000' AND "Order
Master"."ORDNUM_10" < '800000'
AND "Order Master"."STATUS_10" = '3')


If I run just the select query I do get records.

SELECT 'Test String'
FROM "Order Master","Part Master"
WHERE ("Order Master"."PRTNUM_10" = "Part Master"."PRTNUM_01")
AND ("Part Master"."UDFKEY_01" = 'R' OR "Part Master"."UDFKEY_01" =
'RS')
AND "Order Master"."ORDNUM_10" > '700000' AND "Order
Master"."ORDNUM_10" < '800000'
AND "Order Master"."STATUS_10" = '3'


RESULTS:
Test String
Test String
Test String



Any suggestions?
Thanks
-andy

The problem is that your SELECT query is returning THREE values, and
you are trying to assign those values to a single column -- this is
invalid syntax!

The solution is to rework the logic so that the SELECT statement
returns the records you want to change, THEN, remove the "SELECT ...
FROM... WHERE" and replace it with "UPDATE ... SET UDFKEY_10 = 'Test
String' WHERE"
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive.SQL Service & Support - March, 2006 ***
*** Chicago: Pervasive DataExchange Class - March, 2006 ***


BangSQL wrote: