CODEWITHSUNDEEP
sqloracle-databasewhere-clause
K8TE
K8TE

Reputation: 3

How do I update multiple records under one "Where" clause? (Oracle SQL)

I am using Oracle SQL and am having trouble updating a large amount of specific records from my CTRL_NUMBER table. Currently, when I want to only update one record, the following expression works:

UPDATE STOCK
SET PCC_AUTO_KEY=36 WHERE CTRL_NUMBER=54252

But, since I have over 1,000 records to update, I do not want to type this in for each record (CTRL_NUMBER). So I attempted the following with only two records, and the database did not update with the new PCC_AUTO_KEY in the SET condition.

UPDATE STOCK
SET PCC_AUTO_KEY=36 WHERE CTRL_NUMBER=54252 AND CTRL_NUMBER=58334

When I execute the above expression, I do not receive any error codes and it will let me commit the expression, but the database information does not change after I verify the CTRL_NUMBER.

How else could I approach this update effort or how should I change my expression to successfully update the PCC_AUTO_KEY for multiple CTRL_NUMBER?

Thanks for your time!

Upvotes: 0

Views: 2579

Answers (4)

void
void

Reputation: 7890

In your second Update command, you have:

WHERE CTRL_NUMBER=54252 AND CTRL_NUMBER=58334

My question: is it possible for a field to have tow values at a same time in a specific record? of course no.

If you have a range of values for CTRL_NUMBER and you want to update your table on base of them you can do your update with following where clauses:

WHERE CTRL_NUMBER BETWEEN range1 AND range2

or

 WHERE CTRL_NUMBER >= range1 AND CTRL_NUMBER <= range2

But: if you have not an specific range and you have different values for CTRL_NUMBER then you can use IN operator with your where clause:

WHERE CTRL_NUMBER IN (value1,value2,value3,etc)

You can also have your values from another select statement:

WHERE CTRL_NUMBER IN (SELECT value FROM anotherTable)

Upvotes: 3

PAUL BLAKE
PAUL BLAKE

Reputation: 11

Using the IN clause would probably be the best way, as answered by czuroski, the problem with using the AND clause in your statement is that the record would have to have both ctrl_numbers in one record, so 0 records would return and no update would be done (and no error) ... In addition to the IN statement you could use an OR clause and it would be:

... WHERE (CTRL_NUMBER = 54252 OR CTRL_NUMBER = 58334)

Using the ( ) allows you to have other WHERE criteria, but I, too, typically use the IN for multiple requirements in the same field.

Upvotes: 1

uncoder
uncoder

Reputation: 1886

UPDATE STOCK SET PCC_AUTO_KEY=36 WHERE CTRL_NUMBER IN (54252, 58334)

or

UPDATE STOCK SET PCC_AUTO_KEY=36 WHERE (CTRL_NUMBER=54252 OR CTRL_NUMBER=58334)

Upvotes: 1

czuroski
czuroski

Reputation: 4332

Use the IN clause - 

UPDATE STOCK SET PCC_AUTO_KEY=36 WHERE CTRL_NUMBER IN (54252, 58334)

Your statement is trying to update where CTRL_NUMBER is 54252 AND 58334, but it can only be one of those at a time. If you changed your statement to 

UPDATE STOCK SET PCC_AUTO_KEY=36 WHERE CTRL_NUMBER=54252 OR CTRL_NUMBER=58334

it would work.

Upvotes: 1

Related Questions