Update Field with Value from Another Table

[byrne1]

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?

 

[mirtheil]

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

 

[byrne1]

Aha - important information.

PSQL v8.

"Unknown table or correlation name (employee)

 

[mirtheil]

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

 

[byrne1]

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?