• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • FREE workshop: Preparing contractors for Autumn : Weds 29th Sep at 7.15pm. More details here.

SQL question

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    SQL question

    syntax quessie

    i have a table ID,description,somecode (1,000 records)



    I create a second table grouped on ID, descr,0 as noLines from table 1 (50 records)


    I want to update table2.noLines withe the count from table1 where table1.somecode = 2, joined on id



    (\__/)
    (>'.'<)
    ("")("") Born to Drink. Forced to Work

    #2
    Update table2 t2 set nolines = (select count(*) from table1 t1 where t1.id=t2.id and t1.somecode=2)

    It won't be too efficient, I haven't tested it and some versions of SQL aren't happy with it.

    You could just re-create table2 to get all the counts; create table2 as select id, desc, count(*) nolines from table1 group by id, desc


    Note; not sure this is going to work in the way you want, because you may not want to update all the rows in t2.
    Last edited by oraclesmith; 14 May 2009, 10:17.
    It's my opinion and I'm entitled to it. www.areyoupopular.mobi

    Comment


      #3
      Originally posted by oraclesmith View Post
      Update table2 t2 set nolines = (select count(*) from table1 t1 where t1.id=t2.id and t1.somecode=2)

      It won't be too efficient, I haven't tested it and some versions of SQL aren't happy with it.

      You could just re-create table2 to get all the counts; create table2 as select id, desc, count(*) nolines from table1 group by id, desc


      Note; not sure this is going to work in the way you want, because you may not want to update all the rows in t2.
      Fails in sql 2005
      cannot use aggregate in update row


      Indeedy, I have had to create a third table, which as you say, is Sh 1 te



      (\__/)
      (>'.'<)
      ("")("") Born to Drink. Forced to Work

      Comment


        #4
        Originally posted by EternalOptimist View Post
        Fails in sql 2005
        cannot use aggregate in update row


        Indeedy, I have had to create a third table, which as you say, is Sh 1 te



        do it in SSIS if you're using 2005 - and you get to add a new skill to your CV
        I'm sorry, but I'll make no apologies for this

        Pogle is awarded +5 Xeno Geek Points.
        CUK University Challenge Champions 2010
        CUK University Challenge Champions 2012

        Comment


          #5
          Originally posted by Pogle View Post
          do it in SSIS if you're using 2005 - and you get to add a new skill to your CV
          I'm under pressure, dont have time to do it properly.

          byeeeee
          (\__/)
          (>'.'<)
          ("")("") Born to Drink. Forced to Work

          Comment


            #6
            In Oracle SQL you can dynamically create a 'table', which only exists for the duration of the query. It's tough on resources but it might work in other SQL's too.

            eg.

            Update table2 t2 set nolines = (select t3.numlines from
            table1 t1, (select id, count(*) numlines from table1 group by id) t3
            where t1.id = t2.id
            and t1.id = t3.id
            and t1.somecode = 2)


            Usual caveats apply.
            It's my opinion and I'm entitled to it. www.areyoupopular.mobi

            Comment


              #7
              Originally posted by oraclesmith View Post
              Update table2 t2 set nolines = (select count(*) from table1 t1 where t1.id=t2.id and t1.somecode=2)

              It won't be too efficient, I haven't tested it and some versions of SQL aren't happy with it.

              You could just re-create table2 to get all the counts; create table2 as select id, desc, count(*) nolines from table1 group by id, desc


              Note; not sure this is going to work in the way you want, because you may not want to update all the rows in t2.

              Sql server update statment syntax is a bit goofy: I think it should be like

              update table t2 set nolines = t3.numlines from (select id, count(*) numlines from table t1 group by id) t3 where t2.id=t3.id

              which might work...I haven't done T-SQL in a loooong time.
              McCoy: "Medical men are trained in logic."
              Spock: "Trained? Judging from you, I would have guessed it was trial and error."

              Comment

              Working...
              X