Thread: simple? query
<div class="Section1"><p class="MsoNormal">Hi,<p class="MsoNormal"> <p class="MsoNormal">I was trying to run following querybut doesn’t work:<p class="MsoNormal"> <p class="MsoNormal">if (uid='janvleuven10') then<p class="MsoNormal"> insert into test (registered) values ('1');<p class="MsoNormal">else <p class="MsoNormal"> insert into test (registered) values ('0');<p class="MsoNormal">end if;<p class="MsoNormal"> <pclass="MsoNormal"><img height="255" id="Picture_x0020_1" src="cid:image001.png@01CA1C1B.03410D10" width="371"/><p class="MsoNormal"> <p class="MsoNormal">Anyone an idea?<p class="MsoNormal"> <p class="MsoNormal">Regards,<pclass="MsoNormal"> <p class="MsoNormal">Jan<p class="MsoNormal"> </div>
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Jan Verheyden > Subject: [SQL] simple? query > > Hi, > I was trying to run following query but doesn't work: > if (uid='janvleuven10') then > insert into test (registered) values ('1'); > else > insert into test (registered) values ('0'); > end if; Perhaps UPDATE is what you're looking for? http://www.postgresql.org/docs/8.4/static/sql-update.html UPDATE test SET registered = '1' WHERE uid = 'janvleuven10'; UPDATE test set registered = '0' WHERE uid <> 'janvleuven10'; Mike
On 2009-08-13, Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be> wrote: > --_004_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_ > Content-Type: multipart/alternative; > boundary="_000_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_" > > --_000_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_ > Content-Type: text/plain; charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > Hi, > > I was trying to run following query but doesn't work: > > if (uid='janvleuven10') then > insert into test (registered) values ('1'); > else > insert into test (registered) values ('0'); > end if; that's not SQL. (it could be plpgsql) if you need to do it in SQL do this. insert into test (registered) values ( case when uid='janvleuven10' then '1' else '0' end );
> From: Relyea, Mike [mailto:Mike.Relyea@xerox.com] > Sent: Thursday, August 13, 2009 10:47 PM > > > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Jan Verheyden > > Subject: [SQL] simple? query > > > > Hi, > > I was trying to run following query but doesn't work: > > if (uid='janvleuven10') then > > insert into test (registered) values ('1'); else > > insert into test (registered) values ('0'); end if; > > Perhaps UPDATE is what you're looking for? > http://www.postgresql.org/docs/8.4/static/sql-update.html > > UPDATE test SET registered = '1' WHERE uid = 'janvleuven10'; > UPDATE test set registered = '0' WHERE uid <> 'janvleuven10'; > > > > > From: Jan Verheyden [mailto:jan.verheyden@uz.kuleuven.ac.be] > Sent: Friday, August 14, 2009 3:11 AM > To: Relyea, Mike > Subject: RE: [SQL] simple? query > > Hi, > > Thanks for the reply. I was thinking of that, but the problem > is that if it's not registered, the uid is not in the test database... > I think in your example all the other rows will be signed as > 'not registered, is this possible?' > > Regards, > > Jan You're right, my suggestion will not insert new records. It will only update existing ones. And yes, in my example all of the existing rows where uid is not equal to janvleuven10 will have the registered value set to 0. That's how I interpreted the example you gave in your original post. Mike
> From: Jan Verheyden [mailto:jan.verheyden@uz.kuleuven.ac.be] > Sent: Friday, August 14, 2009 9:03 AM > To: Relyea, Mike > Subject: RE: [SQL] simple? query > > The goal is, where uid not equals to 'janvleuven10' a new > record should be inserted with the uid, and registered=0 > > Regards, > > Jan So if a record is found you want to update it and if a record isn't found you want to insert it. I think you'll probably want to use plpgsql http://www.postgresql.org/docs/8.4/static/plpgsql.html or some other language like Jasen suggested. I don't know of a way to do this with straight sql. Mike
"Relyea, Mike" <Mike.Relyea@xerox.com> wrote: >> The goal is, where uid not equals to 'janvleuven10' a new >> record should be inserted with the uid, and registered=0 > So if a record is found you want to update it and if a record isn't > found you want to insert it. I think you'll probably want to use > plpgsql http://www.postgresql.org/docs/8.4/static/plpgsql.html or some > other language like Jasen suggested. I don't know of a way to do this > with straight sql. Something along the lines of: | UPDATE table SET attribute = 'something' WHERE primary_key = 'id'; | INSERT INTO table (primary_key, attribute) SELECT 'id', 'something' WHERE 'id' NOT IN (SELECT primary_key FROM table); should achieve that. Tim