Update Query for multiple rows based on Multiple Criteria

Question

Update Query for multiple rows based on Multiple Criteria

lersner

Right there with Babe

Points: 731
More actions
April 4, 2008 at 8:53 am

#180880

I am trying to write an update query with this as the basic concept:
UPDATE Table1
SET FlagEntity = 0
WHERE (ID = 1) or (ID =2)
I get an Execution error stating that the subquery cannot return more than one value if I use =. How do I get it to allow for this? I also tried putting in a subquery with exists.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

What subquery? The code you posted doesn't have a sub-query, and an UPDATE statement cannot be a subquery.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

If you are using a sub query you want to use IN like this:

Update TableA

Set ColumnA = 'Test'

Where

ColumnB In (Select ColumnB From TableB Where ColumnA = 1)

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

lersner Right there with Babe Points: 731 More actions · Answer 3

Thanks.

How about if I want to do that for multiple records in one update statement. Will I need a loop or can I use an OR?

lersner Right there with Babe Points: 731 More actions · Answer 4

I know that code doesn't have a subquery, I stated that I also tried a subquery using where exists. I have done something like this in Oracle, but never with an Or in the statement.

What I really want to know is if it is even possible to run one update query to update multiple distinct rows or if I need to do a loop.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Could you post some schema info so we can be little clearer on what you want?

You can certainly do:

Update TableA

Set columnA = 1

Where

ColumnB In (Select ColumnA From TableB) Or

ColumnC In (Select ColumnA From TableC)

If that is what you are asking. BTW-ColumnC could just as easily be another criteria on ColumnB.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

lersner (4/4/2008)
Thanks.
How about if I want to do that for multiple records in one update statement. Will I need a loop or can I use an OR?

A single update query can update all of the rows in a table if you so desire. The fact that you're getting errors as to sub-queries is simply telling that a. you're using them, and b. you're using them in such a way that they would require a single value.

Examples:

update table

set datecol=getdate()

would update EVERY row in the table to the current date. If you wanted to be more specific in your update:

update table

set datecol=getdate()

where ID in (1,2,3)

or

update table

set datecol=getdate()

where ID in (select ID from MyOtherTable where dateprocessed='01/01/2006'

Multiple rows isn't an issue.

How about you post what you've tried so far so that we can help you with the specific problem?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

lersner Right there with Babe Points: 731 More actions · Answer 7

lersner

Right there with Babe

Points: 731

April 5, 2008 at 7:32 am

#798773

Thanks I am going to try the where id in (1,2,3).

lersner Right there with Babe Points: 731 More actions · Answer 8

Thanks Matt - the Where in clause worked perfectly! I really appreciate your help.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Something is very wrong here... the original query posted by the OP works just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Jeff Moden (4/5/2008)
Something is very wrong here... the original query posted by the OP works just fine.

Agreed - it seems that we never got to see the actual offending code. But hey - something has apparently helped the OP....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

lersner Right there with Babe Points: 731 More actions · Answer 11

When both id's are found the code does not work. SQL Server showed an error when both conditions were true. So the code did not work if there was both an id of 1 and an id of 2. That is the actual offending code.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Since it worked fine for both Matt and I, I've got to ask... are you actually using SQL Server or are you using some other RDBMS?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shamshudheen SSCrazy Points: 2390 More actions · Answer 13

lersner (4/4/2008)
I am trying to write an update query with this as the basic concept:
UPDATE Table1
SET FlagEntity = 0
WHERE (ID = 1) or (ID =2)
I get an Execution error stating that the subquery cannot return more than one value if I use =. How do I get it to allow for this? I also tried putting in a subquery with exists.

hi

You can try this

UPDATE Table1

SET FlagEntity = 0

WHERE ID IN (1,2)

even your code also work fine. as others asked you provide clear requirement.

lersner Right there with Babe Points: 731 More actions · Answer 14

lersner

Right there with Babe

Points: 731

April 9, 2008 at 6:08 am

#800284

I am using SQL Server 2005.