In SQL how to use if statement using update table command

Question

I am trying to update 2 columns in a table using the update command and I have to use if statement. Table:

SQL> create table report(customer_no number primary key, name varchar2(10) not null, current_meter_reading number not null, previous_meter_reading number not null, category_code number not null, units_consumed number, bill_amount number(5,2), remarks varchar2(20));

For the bill_amount and remarks I need to use if statement, condition:

IF Category code = 1 Bill amount = Units consumed * 0.90, REMARKS = “DOMESTIC”

IF Category code = 2 Bill amount = Units consumed * 2.40, REMARKS = “COMMERCIAL”

IF Category code = 3 Bill amount = Units consumed * 0.50, REMARKS = “FARMERS”

The final output should look like this:Output

What RDBMS are you actually using? Why did you tag this with MySQL, SQL Server and SQLite? They all have their own separate SQL dialects. — Dai
– Dai, Commented Oct 13, 2021 at 4:45
@Gyanesh Then why did you tag with all those other databases? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 13, 2021 at 5:02

Tim Biegeleisen · Accepted Answer · 2021-10-13 05:03:36Z

1

You would use a CASE expression here:

UPDATE report
SET amount = CASE Category_Code WHEN 1 THEN Units_Consumed * 0.90
                                WHEN 2 THEN Units_Consumed * 2.40
                                WHEN 3 THEN Units_Consumed * 0.50 END,
    remarks = CASE Category_Code WHEN 1 THEN 'DOMESTIC'
                                 WHEN 2 THEN 'COMMERCIAL'
                                 WHEN 3 THEN 'FARMERS' END
WHERE Category_Code IN (1, 2, 3);

But note that if the underlying data in the table might change, you should instead consider just using a SELECT with the above logic as computed columns. That is, I am suggesting to not do this update if you expect you might ever have to do it again at a later date.

edited Oct 13, 2021 at 5:03

answered Oct 13, 2021 at 4:40

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Biegeleisen Over a year ago

@Gyanesh I was missing a comma in the SET clause. I have fixed it; please reload your page and use the latest version.

Gyanesh Over a year ago

It is working. Thank you for helping me out.

Collectives™ on Stack Overflow

In SQL how to use if statement using update table command

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related