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
WHERE clauses for targeted updates.
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
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
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
location table’s record and also ensure that a corresponding row exists in
Be sure to see other topics listed on Modern web applications and API with OpenEdge, SQL, ODBC and PHP