Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: Multiple update from a subselect

    Does anyone know whether you can do the equivalent of this SQL Server SQL statement in Oracle?

    Essentially I want to create a subselect and update multiple rows in another table, I have seen lots of examples where you can update a single row/field but not multiple rows.



    This is the SQL Server version :

    update cycles set days = daycount from (select cycle, count(seq) as daycount from shift_cycles, cycles where cycles.name = shift_cycles.cycle group by cycle, days having count(seq) <> cycles.days ) as mytable where cycle = name


    This select works in Oracle :

    select * from (select cycle, count(seq) as daycount from shift_cycles, cycles where cycles.name = shift_cycles.cycle group by cycle, days having count(seq) <> cycles.days) mytable

    However, when I use a similar statement in an update statement, this happens :

    update cycles set days = a.daycount from cycle, (select cycle, count(seq) as daycount from shift_cycles, cycles where cycles.name = shift_cycles.cycle group by cycle, days having count(seq) <> cycles.days) mytable where a.cycle = name
    ORA-00933: SQL command not properly ended



    Thanks in advance,

    Matt

  2. #2
    Join Date
    Sep 2003
    Location
    Netherlands
    Posts
    8
    That's right... you syntax isn't right.

    update cycles set days = a.daycount from cycle, (select cycle, count(seq) as daycount from shift_cycles, cycles where cycles.name = shift_cycles.cycle group by cycle, days having count(seq) <> cycles.days) mytable where a.cycle = name
    ORA-00933: SQL command not properly ended

    shout by something like

    update cycles set days = (select a.daycount from cycle, (select cycle, count(seq) as daycount from shift_cycles, cycles where cycles.name = shift_cycles.cycle group by cycle, days having count(seq) <> cycles.days) mytable where a.cycle = name);

    You forgot the "(select" at the beginning and the ")" at the end.

    make sure the result of your select statement is only one value...

    Grt,
    Jeroen

  3. #3
    Join Date
    Sep 2003
    Posts
    4
    Well this didn't error but it doesn't do exactly what I want.

    This statement :

    update cycles set days = (select a.daycount from cycle, (select cycle, count(seq) as daycount from shift_cycles, cycles where cycles.name = shift_cycles.cycle group by cycle, days having count(seq) <> cycles.days) a where a.cycle = name);

    will update all the rows in the cycles table. I need to specifiy which rows in the cycles table I want to update, which is what the "where" clause is meant to do.

    This is syntactically correct but just sets the days value to null....

    update cycles set days = (select count(seq) from shift_cycles where cycles.name = shift_cycles.cycle group by cycle, days having count(seq) <> cycles.days)

    Cheers,

    Matt

  4. #4
    Join Date
    Sep 2003
    Location
    Netherlands
    Posts
    8
    Hi,

    Yes I only corrected your statement so it was syntacticly correct.

    That the value is null I can't help.

    That all the records are beeing update is because you are, one where clause to short. The last where clause in your first post statement belongs to the select statment and not to the update statement.

    Succes

    Jeroen

Posting Permissions

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