Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...This was the ONLY place that I could find information that I could use to resolve the problem. So thanks once again to member TomSark and the SQL forum!..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Pervasive: Pervasive.SQL Forum

Synching Two Tables

thread318-1391843
Read More Threads Like This One
trbyrne (IS/IT--Management)
25 Jul 07 13:08
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 (Programmer)
25 Jul 07 13:41
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 (IS/IT--Management)
31 Jul 07 15:25
Sorry it's been so long for my reply.  I thought I had Email notification turned on.

The query returns 428 records.
trbyrne (IS/IT--Management)
6 Aug 07 15:25
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 (Programmer)
6 Aug 07 15:44
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 (IS/IT--Management)
6 Aug 07 15:56
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 (Programmer)
6 Aug 07 16:03
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 (IS/IT--Management)
6 Aug 07 16:12
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?
Read More Threads Like This One

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!

Back To Forum

Back To Pervasive: Pervasive.SQL

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