help me in understanding this update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help me in understanding this update

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-25 : 06:30:14

Help needed in understanding the below update statement




update results_key set 

result = temp.tally

from(

        select testid,result,count(*) as tally

        from reslults_emp

        where testid='e001'
        
        group by testid,result

     ) temp

where resluts_key.testid=temp.testid

and results_key.value=temp.result

According to me result expect one and only one value from temp.tally

but it is returning 2 values and the update is working perfectly

updating 2 records.

why I am confused is because I have back ground of Oracle sql plsql and in Oracle

update this is not possible it will give error stating too many rows.

can any body explain me this update statement

Thanks

aak.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 06:35:41

it will look for matching records from temp table based on fields testid and result and do update on results_key table.In cases where you receive more than one values from temp, it wont raise error, but does two updates on same record. the final value of record column depends on what order it retrieved the two records.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-25 : 06:44:49

Thanks Visakh,

If I want to write the same statement in Oracle, can you help me in that
I remember months back I was not able to convert this kind of code to Oracle code
due to this type of update statement.

Really confusing stm MS has offered, as per my understanding '=' sign always expect one and only one value.
multiple value will raise error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 06:51:13

quote:
Originally posted by aakcse

Thanks Visakh,

If I want to write the same statement in Oracle, can you help me in that
I remember months back I was not able to convert this kind of code to Oracle code
due to this type of update statement.

Really confusing stm MS has offered, as per my understanding '=' sign always expect one and only one value.
multiple value will raise error.

This is MS SQL Server forum. Please post in some oracle forums if you need the syntax in oracle. I've not worked in Oracle.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 07:59:39

one of forums you can try is
http://www.dbforums.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources