×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Synching Two Tables

Synching Two Tables

Synching Two Tables

(OP)
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

RE: Synching Two Tables

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

RE: Synching Two Tables

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

The query returns 428 records.

RE: Synching Two Tables

(OP)
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.

RE: Synching Two Tables

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

RE: Synching Two Tables

(OP)
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"

RE: Synching Two Tables

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

RE: Synching Two Tables

(OP)
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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close