Re: Update from Select Query

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@xxxxxxxxxxxxxxxxxxxx
http://www.goldstarsoftware.com
*** Chicago: Pervasive.SQL Service & Support - March, 2006 ***
*** Chicago: Pervasive DataExchange Class - March, 2006 ***


BangSQL wrote:

> 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

.

Relevant Pages

  • Invalid Argument Error
    ... I'm not sure that I'm in the correct group. ... This morning I tried to execute my query. ... I modified my query and I when I went to save it, I got the "Invalid Argument" ... When I tried to compact and repair my db, I got the "Invalid Argument" error. ...
    (microsoft.public.access.setupconfig)
  • Re: A little help on a proc
    ... Do you care if the loginname is invalid? ... > So the best process is to rerun the query? ... >> create proc foo as ...
    (microsoft.public.sqlserver.programming)
  • Re: How do I temporarily limit values in a query field
    ... I am getting an error message: "The expression you entered has a invalid ..or! ... This is the last few lines of the 'old' sql with an attempt to limit the records to values of <500: ... works fine until I try to put limits on it value in the qryClaimDetails query. ...
    (microsoft.public.access.queries)
  • Re: Table and PHP scripts for existing data
    ... >2) once the table is created inside my database on MySQL, ... >load the data in? ... DELETE query"); ... ("Invalid DATA LOAD query"); ...
    (comp.lang.php)
  • Re: Invalid Arguement Error
    ... I'm trying to run a simple select query, and when I try to save the ... I get the error "Invalid Arguement." ... [2006 Voter File].PartyAffiliationDate, [2006 Voter ... dbInconsistent in the options argument to the OpenRecordset method. ...
    (microsoft.public.access.queries)