Update and Select in one query

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

Hello Friends,
I have stuck in updating records. There are two tables
1. tbl_rate with column [rateid(PK),clientid,rvID(FK)]
2. tbl_rateversion with column [rvID(PK),clientid]

I want to update rvID of tbl_rate using condition

tbl_rate.clientid = tbl_rateversion.clientid

Any idea, how can i write query for this??

I have tried

update tbl_rate set rvID = (select distinct tbl_rateversion.rvID from tbl_rateversion
inner join tbl_rate on tbl_rate.clientid = tbl_rateversion.clientid) where ??

But not able to put where condition for it.

Thanks in advance

Posted 20-Apr-11 20:44pm

dhage.prashant01

Add a Solution

Comments

Ankur^\m/ 21-Apr-11 2:57am

You will probably have to use a loop here.

willempipi 21-Apr-11 3:11am

I have the feeling you are making some really simple much more difficult than it is. Insert tbl_rateversion > read it's rvID > then use that to update your tbl_rate record... If I understood wrong please tell us more about what you are trying to do, like when is a tbl_rate record created, and the tbl_rateversion ect....

2 solutions

Solution 2

SQL

UPDATE tr
SET rvID = tresn.rvID
FROM tbl_rate tr
INNER JOIN tbl_rateversion tresn
ON tresn.clientid = tr.clientid
AND (Other Condition )
WHERE ??

More on this You can refer below link
[^]

Posted 20-Apr-11 21:54pm

Pratik Bhesaniya

Updated 20-Apr-11 21:55pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Om Prakash Pant · Accepted Answer · 2011-04-20T21:07:00

You can try something like:

SQL

update tr
    SET
      rvID = trr.rvID
FROM tbl_rate tr
    JOIN tbl_rateversion trr ON trr.clientid = tr.clientid
WHERE  . . . .(add other condition) ...

If you are using SQL 2008, then you can use Merge statement. Example:

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/[^]