Complex update with SQL in OpenEdge

We covered Update data in OpenEdge using SQL and ODBC in a previous post. In this post we will review complex update with SQL. Let’s assume that you have a client such as DBeaver set up. Connect to your OpenEdge database using ODBC. In my examples, I am using Sports2000 database. We will run through situations where update is done on multiple columns using literal data or data from another table. We’ll use JOIN and WHERE clauses for targeted updates.

Individual update

Let’s update a department name from Testing to Testing only. Always remember to use a WHERE clause, without which the entire table can get updated. If you plan to update the entire table, great…leave out the WHERE clause.

UPDATE pub.department
SET deptname = 'Testing only'
WHERE deptcode = '1000';

Update multiple columns

Let’s update multiple columns in the department table for a single row.

UPDATE department
SET deptcode = '2000',
 deptname = 'Testing only'
WHERE deptcode = '1000';

Update a table using data from another table

To demonstrate the example of updating one table using data from another table, let’s create a dummy table called location and enter some data in it. We will update data in location table using data from warehouse table.

UPDATE department
CREATE TABLE location (
	id INT PRIMARY KEY,
	name varchar(60),
	state varchar(40)
);

INSERT INTO location(id) VALUES (1);
INSERT INTO location(id) VALUES (10);

SELECT * FROM location;

ID |NAME |STATE |
---|-----|------|
1  |     |      |
10 |     |      |

Great! Now, let’s update these 2 rows with data from warehouse.

UPDATE location
SET (name, state) = (SELECT warehousename, state FROM warehouse WHERE warehousenum = location.id)
WHERE EXISTS (SELECT 1 FROM warehouse WHERE warehousenum = location.id);

SELECT * FROM location;

ID |NAME           |STATE |
---|---------------|------|
1  |Northeast  USA |MA    |
10 |Milano Italia  |      |

As you may have noticed, we can use SELECT statement that retrieves one and only one row from warehouse matching location table’s record and also ensure that a corresponding row exists in warehouse table.

Be sure to see other topics listed on Modern web applications and API with OpenEdge, SQL, ODBC and PHP