Update Table with a "Select query" with a where clause

Question

I want to achieve the following:

Current State of table (my_table)

 id        totalX          totalY          totalZ               
 --------- --------------  --------------  --------------       
         9             34              334             0      
        10              6               56             0      
        11             21              251             0      
        12              3               93             0   

Query result of (my_table2)

select id,count(*) as total FROM my_table2 WHERE column_2 = 1 GROUP BY id

 id        total               
 --------- --------------       
         9            500      
        10            600      
        11            700      
        12            800  

Expected State of table (my_table)

 id        totalX          totalY          totalZ               
 --------- --------------  --------------  --------------       
         9             34              334             500      
        10              6               56             600      
        11             21              251             700      
        12              3               93             800    

Can this be done in ONE update query ? I am looking for Sybase ASE 12.5 on a RHEL 5.0

EDIT: I coudn't find the solution for Sybase, but the current answer to the question works on MS SQL Server..

Solution 1

   update 
          my_table 
   set 
      my_table.totalZ = t.total 
   FROM
    my_table mt
    INNER JOIN 
       (select id,count(*) as total 
       FROM my_table2 
      WHERE column_2 = 1 GROUP BY id) t
   on mt.id  = t.id

UPDATE In MS SQL Server this is what you would do. The OP noted this doesn't work in Sybase.

Solution 2

something like this should do the trick

update my_table
set my_table.totalZ = (select count(*) from my_table2 where my_table.id = my_table2.id and my_table2.column_2 = 1);

Comments

[Stewie]

This produces error: Msg 11762, Level 15, State 1: Server 'DEVDB1', Line 1: You cannot use a derived table in the FROM clause of an UPDATE or DELETE statement

[Conrad Frix]

@Stewie Sorry about that. I should have included in my answer that I hadn't checked Sybase but works in MS SQL Server.

[Stewie]

Thats what I guessed. It should work with MySQL too .. I guess Sybase is just being a Bi**h

[dburges]

Does sybase have temp tables? PUt the results of the query in a temp table and then join to that inthe update.

[Conrad Frix]

@HLGEM That wouldn't be one query would it.

[aF.]

Sybase doens't allow derived tables with group by in update or delete statements. As @HLGEM stated, you can use temp tables to solve this problem.

[Hambone]

I figured there was no way this would work, but it did. A very late thank you.