Re: Syntax error? What syntax error??
In article <EAv3rK.Js0@nonexistent.com>, Jacob says...
>
>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?|
>+----------------------------------------------------------+
Jake,
The subquery MUST be in double brackets. This is actually an error in Informix
docs - some examples are incorrect.
However, if you check those clever diagrams carefully, you can see that double
brackets are required, not singles.
I ****ed up with this about 6 months ago myself, took some time to figure.
Cheers
Ilya