Synching Two Tables

[trbyrne]

I'm trying to sync the Email address of one table with the Email address of another table. I'm using the following query but I'm getting an "invalid row count in subquery" error.

update custrel set primemailaddress=(select customer.emailaddress from customer,custrel where emailaddress<>'' and primemailaddress='' and
customer.customernum=custrel.customernum)

I'm running PSQL v8.6

 

[mirtheil]

How many records does the query:
select customer.emailaddress from customer,custrel where emailaddress<>'' and primemailaddress='' and
customer.customernum=custrel.customernum
return?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[trbyrne]

Sorry it's been so long for my reply. I thought I had Email notification turned on.

The query returns 428 records.

 

[trbyrne]

I am also getting this message for this statement:

update event set webxmitflag='Y' where customernum=(select customernum from customer where accounttype='GLB' and emailaddress like 'no%')

It returns with an error "Invalid row-count in subquery". The subquery, if run alone, returns 50 rows.

There is obviously something common I am doing wrong with the syntax of these two UPDATE statements, but I cannot figure it out. There's not much on the internet when I do a search for this particular error message.

 

[mirtheil]

WHat happens if you explicitly state which table each field is from in the sub query.
For example:
UPDATE T1 SET T1.C2 = (SELECT T2.C2 FROM T2 WHERE T2.C1 = T1.C1)



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[trbyrne]

I ran this update statement:

"update event set event.customernum=(select customer.customernum from customer where customer.customernum=event.eventnum)"

and I get this error message:

"Column <CustomerNum> not nullable"

 

[mirtheil]

Sounds like:
select customer.customernum from customer where customer.customernum=event.eventnum
is returning an empty result set.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[trbyrne]

When I run that query by itself, it returns:

unknown table or correlation name [event]


which makes sense as I have not referenced the EVENT table. But the syntax follows the syntax that you posted earlier in a reply.

This all comes out of the issue I am having with the "invalid row-count in subquery" error on my UPDATE statement. The latest subquery returns 50 rows of data. For some reason I think that it is trying to update one row with the 50 rows??? So how do I reconstruct the UPDATE statement to accomodate these 50 rows?