In SQL how to use if statement using update table command-CodePudding

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

CodePudding user response：

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.