Thread: select in update
Hi all, Yet another question: (quite busy today... :) oeptest=> update shorttest2 set b=(select b from shorttest t1 where t1.a=1) where a=2; ERROR: parser: parse error at or near "select" It means that the subquery in update is not implemented? And if it isn't, how can I evade it? Maybe select ... into .., but it's not a good idea in a big table... :((( Any help? Thx a lot: Circum __ @ / \ _ _ Engard Ferenc l | ( \ / | | (\/) mailto:s-fery@kkt.sote.hu \__/ | | \_ \_/ I I http://pons.sote.hu/~s-fery
At 1:41 +0200 on 24/11/98, Engard Ferenc wrote: > Yet another question: (quite busy today... :) > > oeptest=> update shorttest2 set b=(select b from shorttest t1 where >t1.a=1) where a=2; > ERROR: parser: parse error at or near "select" > > It means that the subquery in update is not implemented? And if it isn't, > how can I evade it? Maybe select ... into .., but it's not a good idea in a > big table... :((( > > Any help? The long-standing syntax for this in PostgreSQL is: UPDATE shorttest2 SET b=t1.b FROM shorttest t1 WHERE t1.a=1; Please, look up the manpages for the command you want to invoke, or \h it. You may discover syntax options that cover what you need. I have PostgreSQL 6.2.1, and running "\h update" in psql yields: testing=> \h update Command: update Description: update tuples Syntax: update <class_name> set <attr1>=<expr1>,...<attrN>=<exprN> [from <from_clause>] [where <qual>]; Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
On Tue, 24 Nov 1998, Herouth Maoz wrote: >The long-standing syntax for this in PostgreSQL is: > >UPDATE shorttest2 >SET b=t1.b >FROM shorttest t1 >WHERE t1.a=1; > >Please, look up the manpages for the command you want to invoke, or \h it. >You may discover syntax options that cover what you need. I have PostgreSQL Thx for the help! Anyway, I have read the docs, but since there is no example there, and I didn't know this syntax (and maybe my english is not so good), I couldn't figure out what is this strange FROM clausule... :)) Well, then a little bit complex problem: two tables: A (var), B (var1,var2). I would like to replace the A.var if it exists in B.var2 with B.var1. Is it good? UPDATE a SET a.var=b.var1 FROM b <-- or maybe I need to put 'a' here too? WHERE a.var=b.var2; I don't need to tell that there are rows which cannot find in B? (In this case it shouldn't change.) I feel that I need to write two different WHERE-s: one for the subquery (which then results 1 row), and one for the UPDATE, to tell where to put that value. I solved this until now with a 'c' prg. Thanks again and bye: Circum __ @ / \ _ _ Engard Ferenc l | ( \ / | | (\/) mailto:s-fery@kkt.sote.hu \__/ | | \_ \_/ I I http://pons.sote.hu/~s-fery
At 12:05 +0200 on 24/11/98, Engard Ferenc wrote: > Well, then a little bit complex problem: > two tables: A (var), B (var1,var2). I would like to replace the A.var > if it exists in B.var2 with B.var1. Is it good? > > UPDATE a > SET a.var=b.var1 > FROM b <-- or maybe I need to put 'a' here too? > WHERE a.var=b.var2; > > I don't need to tell that there are rows which cannot find in B? (In > this case it shouldn't change.) It's a bit hard to understand what you want to be done, and what you *don't* want to be done. The above seems to be the right update. But you better give a short example of what you want. In the example you gave, if the data is: A == 1 2 3 4 B == var1 var2 ==== ==== 10 1 30 3 50 5 Then after the update, A will be: 10 2 30 4 This is because 1 and 3 were matched, and 2 and 4 weren't matched. If that is what you wanted, then your update statement is correct. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma