I tried executing this query:
Update BVTF a
set a.CustID = (Select b.CustID from Lines b
                 where b.TN = a.TFNumber)
where a.CustID is NULL


and I received this error
ElevateDB Error #1011 An error occurred with the query (SELECT ALL
"b"."CustID" AS "CustID" FROM "Lines" AS "b" WHERE "b"."TN" =
"a"."TFNumber") (A scalar query can only return a single value)

CustID is an integer field
TN and TFNumber ar VarChars

What is the correct way to write this.

Thanx
Ben

Ben


The problem is that your sub-select is returning more than  one row. If this is expected then you can force a single row by the use of RANGE or possibly DISTINCT.

Firstly I'd run the sub-select as a stand alone query and find what its actually producing.

Roy Lambert

Thanks .  Select distinct did it

"Roy Lambert"  wrote in message
news:1061BFB3-C39A-4CA2-AD89-A1D7C6206C87@news.elevatesoft.com...

Ben


The problem is that your sub-select is returning more than  one row. If this
is expected then you can force a single row by the use of RANGE or possibly
DISTINCT.

Firstly I'd run the sub-select as a stand alone query and find what its
actually producing.

Roy Lambert