Обсуждение: 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