×
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

Update Field with Value from Another Table

Update Field with Value from Another Table

Update Field with Value from Another Table

(OP)
I am trying to execute this script but I get an error:

update CUSTREL set PREFSTORE = EMPLOYEE.STORENUM where EMPLOYEE.NUM=CUSTREL.PREFSLSP

Basically I want to update the customer's preferred store location based on the assigned store location of their preferred salesperson.  My tables (in a nutshell) are as follows:

CUSTREL
<PrefSlsp>
<PrefStore>

EMPLOYEE
<Num>
<StoreNum>

What script can I use to accomplish this simple task?

RE: Update Field with Value from Another Table

A few questions:
- What version of PSQL are you using?
- What error do you get?  

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Update Field with Value from Another Table

(OP)
Aha - important information.

PSQL v8.

"Unknown table or correlation name (employee)"

RE: Update Field with Value from Another Table

Maybe something like:
update CUSTREL set CUSTREL.PREFSTORE = (select EMPLOYEE.STORENUM where EMPLOYEE.NUM=CUSTREL.PREFSLSP)

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Update Field with Value from Another Table

(OP)
Now I get an error message saying "Column (PrefStore) not nullable".  This, to me, indicates one of the following:

- I have a null value in a record for the field EMPLOYEE.STORENUM (I checked and this is not the case)
- I have a value in CUSTREL.PREFSLSP that is not represented in the EMPLOYEE table (perhaps a salesperson was assigned to a customer and that salesperson is no longer with us, thus is not in the EMPLOYEE table)

If the later is true, how can I handle it within the SQL script?

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