Re: Syntax error? What syntax error??

Yesterday, I asked: Why does the following work with no problem: > update orders > set lastname > = (select lname > from customer c > where c.customer_num = orders.customer_num) while the following gets a syntax error? > 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: Mark and Rudy both answered that I need an extra pair of parentheses; my update statement should look like: 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)); Notice that the subquery is surrounded by 2 pairs of parentheses. FYI, this DOES work and produces exactly the result I wanted. With 20/20 hindsight, I'd like to add an explanation of the difference between these update statements - why the 1-column test worked with only one pair of () and why the 2 pair are required. I might even make it sound logical. (I might also sell a pair of shoes to a snake. Here is a 1-column UPDATE statement: update customer set lname = "Smith" where lname = "Wesson" No () needed because it is only a single column being updated. Suppose, now, that the value being assigned is the output of a subquery. This DOES require a () around the subquery: update customer set lname = "Smith" where lname = (select lname from yutz where id = 12345) Come back to assigning values directly, not with a subquery, but multiple columns. This also requires (): update customer set (fname, lname) = ("Larry", "Smith") where fname = "Lorenzo" and lname = "Smythe" Yes, the list of target columns requires the (). But more important, the list of values requires them. This is ordinary SQL requirement, which we grumble about but we know where it stands. So we now have two situations that require parentheses: 1. A list of assigned values - more than one assigned value. 2. A subquery. My situation had both of these circumstances: It involved a subquery and the subquery returned a list of assigned values. Hence, the syntax demanded a pair of parentheses to cover each situation. i.e. ((subquery)). Intuitive? Nah! Logical? That's arguable. It does tell me a little about the implementation of the syntax and how these constructs are parsed by the Informix SQL parser. I have no access to any other vendor's SQL at this time and no time to experiment now anyway. But, in answer to Rudy's remark, it probably IS ASNI-SQL standard. Thanks all for the help. -- -- Jake (In pursuit of undomesticated aquatic avians) +----------------------------------------------------------+ |Aside from that, how did you enjoy the play, Mrs. Lincoln?| +----------------------------------------------------------+