tricky case problem 
Author Message
 tricky case problem
Hello

I have this table

newsid (PK/ID), newsdate (datetime), headline

and I would like it to just have a max of 2 rows inserted.

here's some psuedo code:

case count(newsid) <2 then
   insert
case count(newsid) =2 then
   update the oldest one

how exactly is it done?

/Lasse



Sun, 16 Feb 2003 03:00:00 GMT
 tricky case problem

Try this :





  begin
   <insert new record >
  end

  begin


  end

Brett W. Green
Senior Engineer
IXI Corporation



Quote:
> Hello

> I have this table

> newsid (PK/ID), newsdate (datetime), headline

> and I would like it to just have a max of 2 rows inserted.

> here's some psuedo code:

> case count(newsid) <2 then
>    insert
> case count(newsid) =2 then
>    update the oldest one

> how exactly is it done?

> /Lasse

Sent via Deja.com http://www.deja.com/
Before you buy.


Sun, 16 Feb 2003 03:00:00 GMT
 tricky case problem
hmm.... there must be an easier way without variables and stuff?
Quote:

> Try this :





>   begin
>    <insert new record >
>   end

>   begin


>   end

> Brett W. Green
> Senior Engineer
> IXI Corporation



> > Hello

> > I have this table

> > newsid (PK/ID), newsdate (datetime), headline

> > and I would like it to just have a max of 2 rows inserted.

> > here's some psuedo code:

> > case count(newsid) <2 then
> >    insert
> > case count(newsid) =2 then
> >    update the oldest one

> > how exactly is it done?

> > /Lasse

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Sun, 16 Feb 2003 03:00:00 GMT
 tricky case problem
Two rows is a small table, but that part is easy:

CREATE TABLE NewsClipping
(newsid INTEGER NOT NULL PRIMARY KEY
        DEFAULT 1
        CHECK (newsid IN (1,2)),
 newsdate DATE NOT NULL,
 headline VARCHAR(50) NOTB NULL);

Switching out the oldest headline is a little tricker.  That row will
have the earliest of the two dates, but I also want to change the other
row to keep the newsid numbers in sequential order.

UPDATE NewsClippings
   SET newsdate = CASE
                  WHEN newsdate
                       =(SELECT MIN(newsdate)
                           FROM NewsClippings)
                  THEN :my_new_newdate
                  ELSE newdate END,
       headline = CASE
                  WHEN newsdate
                       =(SELECT MIN(newsdate)
                           FROM NewsClippings)
                  THEN :my_new_headline
                  ELSE headloine END,
       newsid = CASE
                WHEN newsdate
                     =(SELECT MIN(newsdate)
                           FROM NewsClippings)
                THEN 2
                ELSE 1 END;

You will have to start this table with two rows for this to work.

--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.



Sun, 16 Feb 2003 03:00:00 GMT
 tricky case problem
If you like, you can avoid any variables or if-then code by simply doing
your insert without regard to the number of existing records and then
executing this statement to remove any rows older than the last two:

DELETE newstable
WHERE (SELECT COUNT(*) FROM newstable t WHERE t.newsdate >
newstable.newsdate) > 1

--
Rich Dillon
Acadio Corporation
http://acadio.com


Quote:
> hmm.... there must be an easier way without variables and stuff?


> > Try this :





> >   begin
> >    <insert new record >
> >   end

> >   begin


> >   end

> > Brett W. Green
> > Senior Engineer
> > IXI Corporation



> > > Hello

> > > I have this table

> > > newsid (PK/ID), newsdate (datetime), headline

> > > and I would like it to just have a max of 2 rows inserted.

> > > here's some psuedo code:

> > > case count(newsid) <2 then
> > >    insert
> > > case count(newsid) =2 then
> > >    update the oldest one

> > > how exactly is it done?

> > > /Lasse

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.



Sun, 16 Feb 2003 03:00:00 GMT
 tricky case problem
Hey Joe,

You seem like a man that knows what he's doing with his T-SQL, I dont
suppose you've seen my threads "Can anybody help me?" or "Combining a
trigger and a stored procedure", I really am in need of some help with a
thorny T-SQL problem.

Cheers
Jason

PS, Sorry for being so cheeky, but Im getting desperate now!


Quote:
> Two rows is a small table, but that part is easy:

> CREATE TABLE NewsClipping
> (newsid INTEGER NOT NULL PRIMARY KEY
>         DEFAULT 1
>         CHECK (newsid IN (1,2)),
>  newsdate DATE NOT NULL,
>  headline VARCHAR(50) NOTB NULL);

> Switching out the oldest headline is a little tricker.  That row will
> have the earliest of the two dates, but I also want to change the other
> row to keep the newsid numbers in sequential order.

> UPDATE NewsClippings
>    SET newsdate = CASE
>                   WHEN newsdate
>                        =(SELECT MIN(newsdate)
>                            FROM NewsClippings)
>                   THEN :my_new_newdate
>                   ELSE newdate END,
>        headline = CASE
>                   WHEN newsdate
>                        =(SELECT MIN(newsdate)
>                            FROM NewsClippings)
>                   THEN :my_new_headline
>                   ELSE headloine END,
>        newsid = CASE
>                 WHEN newsdate
>                      =(SELECT MIN(newsdate)
>                            FROM NewsClippings)
>                 THEN 2
>                 ELSE 1 END;

> You will have to start this table with two rows for this to work.

> --CELKO--
> Joe Celko, SQL and Database Consultant
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
> which can be cut and pasted into Query Analyzer is appreciated.

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Mon, 17 Feb 2003 19:11:49 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. tricky CASE WHEN query...

2. tricky join with case

3. collation problem -- switch from case insensitive to case sensitive

4. Tricky Counter Problem

5. Tricky order by problem

6. Tricky Query Problem

7. Tricky Time problem

8. Tricky view/UDF/storedprocedure problem

9. Tricky Date Problem

10. Select statement tricky problem

11. A tricky SQL problem

12. Tricky SQL Problem


 
Powered by phpBB® Forum Software