Help with query - SQL Server Forums

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 with query

Author

Topic

rkalyani
Starting Member

30 Posts

Posted - 2009-03-05 : 13:17:19

I need to write a sql query to do this but i am not sure how to do it.

I have a table like this

prd Smkr Gender Rate Age
11 N F 100 29
11 N F 150 30
11 N F 150 31
11 N F 150 32
11 N 50 0
11 N 75 1

Now I need to update rows for product 12. I need it as an update because I already have rows in it. THe update should just caluculate different rate for a different product number but the rest of the information will be same. So the result will be like this

prd Smkr Gender Rate Age
12 N F 120 29
12 N F 160 30
12 N F 160 31
12 N F 160 32
12 N 60 0
12 N 80 1

Is there a SQL way to do this. I wrote code in .net to loop through and do it but I was wondering if I could write a sql query to do this.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 13:20:18

you can use UPDATE table SET...WHERE prd=12 to update all the records with product 12 in a batch.

rkalyani
Starting Member

30 Posts

Posted - 2009-03-05 : 13:25:28

Sorry, I should have been clear. I already have the rows in the first table - prd_id =11. Now I need to go through every row in prd_id 11 and update the values in prd_id 12 with the new rate. I will get the new rate as a multiple of the rate in prd_id 11. So the columns and most of the values are the same except the rate.

Thank you

Thank you,
kal30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 13:30:32

then just do


UPDATE t1
SET t1.Rate=t2.Rate* your multiplying factor
FROM Table t1
JOIN table t2
ON t1.Age=t2.Age
WHERE t1.prd=12
AND t2.prd=11

rkalyani
Starting Member

30 Posts

Posted - 2009-03-05 : 13:44:14

One more question. Both the fields are in the same table. I mean prd_id 11 and prd_id 12 are in the same table. Can I join values in same table.

Thank you

Thank you,
kal30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-07 : 13:48:00

yup you can . as you see in my query. i'm using same table twice. just use different aliases (t1 & t2 in my example)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources