UPDATE (subquery) SET Column='1'

Question

0.00/5 (No votes)

See more:

Hi

I want to update one of the columns of my table.

I have to use subquery to reach to the desired record, but it seems that we aren't allowed to use subquery in front of UPDATE.

what is the best way to update the column with subquery ?

SQL

UPDATE 	(SELECT TOP(1)* FROM (SELECT * FROM TempTable WHERE Column1='1' AND Column2='2') AS AAA )AS BBB
	
		SET Column3='1'

Posted 13-Jul-13 20:32pm

Behno0o0oD

Add a Solution

3 solutions

Solution 2

Hi
If your table has a unique identity columns as ID, you can use from bellow query:

SQL

UPDATE 	TempTable
	WHERE	ColumnID = (Select	Top 1   ColumnID
				From	TempTable
				Where	Column1='1' AND Column2='2')
	SET Column3 = '1'

Please use from this query.

Posted 13-Jul-13 21:55pm

Reza Alipour Fard

Updated 13-Jul-13 22:36pm

v2

Comments

Behno0o0oD 14-Jul-13 4:16am

I tried this but it seems that there is a problem with "TOP 1 ColumnID".

Maybe because of SQLServer CE!

Reza Alipour Fard 14-Jul-13 4:36am

You must use '(' before select and ')' in enf of select.
Please attention bellow query:
UPDATE TempTable
WHERE ColumnID = (Select Top 1 ColumnID
From TempTable
Where Column1='1' AND Column2='2')
SET Column3 = '1'

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)

Reza Alipour Fard · Accepted Answer · 2013-07-13T22:36:00

Solution 3

SQL

UPDATE 	TempTable
	WHERE	ColumnID = (Select	Top 1   ColumnID
				From	TempTable
				Where	Column1='1' AND Column2='2')
	SET Column3 = '1'

Posted 13-Jul-13 22:36pm

Reza Alipour Fard

Reza Alipour Fard · Accepted Answer · 2013-07-13T20:48:00

Solution 1

Hi
If your table has a unique identity columns as ID, you can use from bellow query:

SQL

Declare	@RowID	As	BigInt
Set	@RowID	=	Select	Top 1   ColumnID
				From	TempTable
				Where	Column1='1' AND Column2='2'
UPDATE 	TempTable
	WHERE	ColumnID = @RowID
	SET Column3 = '1'

I hope it's helpful for you.

Posted 13-Jul-13 20:48pm

Reza Alipour Fard

Comments

Behno0o0oD 14-Jul-13 2:56am

TnX

It seems that I have to add ID Column;)

Reza Alipour Fard 14-Jul-13 3:36am

I have a suggestion for you: Add unique identify column as ID to all tables. This help you to easily write query for your purpose. If my answer it's OK for you please give rate 5 (out of 5) to my answer. Best regards

Behno0o0oD 14-Jul-13 3:36am

It seems that SQLServer CE does not support variables. What is the next solution?
TnX

Reza Alipour Fard 14-Jul-13 3:54am

See solution 2.

_Asif_ 14-Jul-13 5:40am

instead of adding more solutions you should improve your first solution.

Reza Alipour Fard 14-Jul-13 5:53am

You are right