Syntax error? What syntax error??
OnLine 7.13.UC5
HP-UX 10.10 Model 869
Hi family,
I'm having grief from an update command that worked in the stores
database but is failing with a syntax error in my live database.
Forget normalization for a moment; I didn't design this. But the basic
idea is to propagate a column (or set of columns) from all master rows
to all detail rows. To test this, I added a "lastname" column into the
orders table and ran a bizarre looking update to propagate the lname
column from the customer rows into the new lastname column in orders.
The following worked in a stores database:
update orders
set lastname
= (select lname
from customer c
where c.customer_num = orders.customer_num)
Yeah, dbaccess complained that there was no WHERE clause on the outer
query, but the results are OK. Observe the subsequent query:
select order_num, orders.customer_num, lastname, fname, lname
from orders, customer
where orders.customer_num = customer.customer_num
order_num customer_num lastname fname lname
1001 104 Higgins Anthony Higgins
1002 101 Pauli Ludwig Pauli
--- SNIP -- (it worked)
All this to establish credibility with an incredible update statement.
(I feel like choking myself for coming up with it ;-)
Now, I try it on a table in the live database, but this time I am
propagating several columns:
update foggy_stor
set (country, old_acct, cust_type, call_alloc, ship_lead)
= (select country, old_acct, cust_type, call_alloc, ship_lead
from customer c
where c.customer = foggy_stor.customer);
Aside from the number of columns involved, the only difference is the
parentheses around the list of columns to be updated. And this is a
syntax requirement. Anyway, when I run the above update, I get this
error output:
update foggy_stor
set (country, old_acct, cust_type, call_alloc, ship_lead)
= (select country, old_acct, cust_type, call_alloc, ship_lead
# ^
# 201: A syntax error has occurred.
#
from customer c
where c.customer = foggy_stor.customer);
In case you are viewing news with a variable font, the ^ is under the
letter 'c' of the word 'country' in the subquery.
I am looking at the two queries side by side and I cannot see what the
syntax error could possibly be. What am I missing here?
Thanks.
--
-- Jake (In pursuit of undomesticated aquatic avians)
+----------------------------------------------------------+
|Aside from that, how did you enjoy the play, Mrs. Lincoln?|
+----------------------------------------------------------+