Lets say I create a DB with tables for a movie rental data Base. I have a table called customer and in this table I have late_fees and PK_Customer_ID attributes.
What I am trying to do is on a promotion waive 20% off total late fees. I would like to select the customers that are getting this late fee reduction.
What I have so far is
update Customers
set Customer_Balance_Due = (Customer_Balance_Due -(Customer_Balance_Due * .20))
where PK_Customer_ID = 1;
select * from Customers
What I would like to do is add multiple PK_Customer_ID values in the WHERE clause. Is this possible in one statement like this or would I need to write each one out when I do an update like this?
I think in the end it would be better to do a where Customer_Balance_Due > 0 that way it automatically selects customers that have a valid balance. but for the sake of asking. Can you do a single statement multi row update?
when adding commas in the where statement that would give an incorrect syntax error so that would not work I was hoping the syntax would be right and it would allow me to write out a large update in a one line statement.
>Solution :
You can write a condition like this:
WHERE PK_Customer_ID IN (1, 2, 3)