Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2011
    Posts
    3

    Unanswered: Using Update with Group By

    Ok I am searching forums with some luck, but meanwhile thought to post:

    Table RED has this column X whose content can be non-unique.

    If the record is unique I would like to set the column Y in the record s to 0.

    I had something like:

    UPDATE (select Y , x, COUNT(x) FROM RED where ID = 453 GROUP BY X HAVING
    COUNT(X) = 1) SET RED.Y = 0

    But apparently Y is not valid here.....

    Then I tried

    UPDATE RED SET Y=0
    WHERE x in (select x, COUNT(x) FROM RED where ID = 453 GROUP BY X HAVING
    COUNT(X) = 1);

    Now there is only 20 records returned by the sub query but Oracle says : Error, too many records!

    How else can I approach this?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL> @red
    SQL> create table red (x number, y number);

    Table created.

    SQL> insert into red values(1,1);

    1 row created.

    SQL> insert into red values(1,1);

    1 row created.

    SQL> insert into red values(2,2);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> update red set y = 0 where x in (select x from (select x,count(x) from red having count(x) =1 group by x));

    1 row updated.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2011
    Posts
    3
    Hi,

    Thanks for the reply.

    The problem is I get an error saying I have too many records form subquery, I am not sure why it is too many, it is roughly 23 in total.

    I should look at your statement again to see what you might be doing different. Are you saying the table variable removes this problem?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I don't know what you have.
    I don't know what you do.
    I don't know what you see.
    It is really, Really, REALLY difficult to fix a problem that can not be seen.
    use COPY & PASTE so we can see what you do & how Oracle responds.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2011
    Posts
    3
    thanks. It was solved. Keeping that table variable helped. I basically had to tweak. Forgive my ignorance at the beginning.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A little shorter would be

    Code:
    update red set y = 0 where x in (select x from red group by x having count(*) = 1);
    You don't have to have count(*) in the select to use it in the having.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •