Member Avatar for grin2dan

Hello All, I need to generate a simple script according to the following: "Using only one update write query to update a salary by 10% for salaries below 100k and 15% for salaries above 100k"
TIA

  • 3 Contributors
  • 3 Replies
  • 88 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by grin2dan

Dani AI

Generated

A single UPDATE with a CASE expression is the right pattern here; and pointed that out. Note the numeric factors: a 10% increase is 1.10 and a 15% increase is 1.15 — 1.5 would be a 50% increase, so double-check the constants. Also decide how to treat a salary equal to 100000 up front (treat as lower, higher, or leave unchanged) and make that explicit in the condition you use.

Before running the write, preview and protect the change. Count rows in each bucket (<100000, =100000, >100000) and rows with NULL salaries; check the salary column type and precision so multiplication does not overflow or truncate. Run the update inside a transaction (or use a SAVEPOINT), verify results with SELECT, then COMMIT. To reduce redo/undo and avoid touching unchanged rows, add a WHERE that limits the update to only the ranges you mean, and for very large tables perform batched updates by primary-key ranges. If exact cents matter, use rounding to the needed scale. Remember updating a column that is indexed or referenced by triggers will maintain indexes and fire triggers, so plan for that overhead.

For audit or verification capture old/new values (for example with RETURNING in PL/SQL or a simple audit table populated by a trigger). Test the full sequence in a dev copy first and have a rollback plan before changing production data.

Recommended Answers

All 3 Replies

Member Avatar for anubina 
update employee set salary = salary*(case when salary < 100000 then 1.5 when salary > 100000 1.1 else 1 end);

You can use a CASE ... END stament for address the factor of multiplie salary. And when the salary = 100.000 exactly? Well in this case the factor is 1 (you don`t specified this case).
When the field has a null value, the factor is 1*null=null, all ok.

I know... my level english is very low.

Greetings

PS. Do you need a script in Batch-dos or PL/SQL?

Member Avatar for cgyrob

This should work for you.

update employee set salary = case 
          when salary <    100000 then  salary * 1.10
          when salary >=  100000 then  salary * 1.15
End;
Member Avatar for grin2dan

Thanks all for the great answers! Dan

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.