Updating multiple columns

[jrponline]

Hi There,

I need some helping building an update query in Pervasive SQL Data Manager V8.6

I have two tables, one called INVENTORY, and another called CDN_UPDATES

The tables have the same field names, and data types.

I need to update the records in INVENTORY with the new more current data from all the fields in CDN_UPDATES where the part numbers (CODE column) in both tables match.

There are about 7 fields in this update, eg: price, quantity, description, etc.

Can you help?

 

[mirtheil]

What have you tried? What error (or behavior) are you seeing?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[jrponline]

Well, I'm quite new at this, so I have only made a query that can update one field. Here is what I have.

UPDATE "INVENTORY" SET "INVENTORY"."WHOLESALE" = (SELECT "CDN_UPDATES"."WHOLESALE" FROM "CDN_UPDATES" WHERE "CDN_UPDATES"."CODE" = "INVENTORY"."CODE")

The problem with my query is that

1) it updated WHOLESALE in every record of INVENTORY, to a ZERO if it doesn't match up the CODE

2) it only updates WHOLESALE, I need to update several more columns than that

 

[mirtheil]

If you're new to SQL, I would suggest looking at the docs for the UPDATE statement. Here's a link to the PSQL 8.7 UPDATE statement (it's the same for 8.6 and 9.x):

http://www.pervasive.com/library/docs/psql/870/sqlref/sqlref-4-70.html

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[jrponline]

I'm not new to SQL, I am just new to Pervasive, in SQL this would be simple, just do an inner join. Pervasive is very very picky.

 

[jrponline]

Can anyone help me with this?