CODEWITHSUNDEEP
sqloracle-databaseplsql
Смок Белью
Смок Белью

Reputation: 25

How can I update and divide column values?

I have a table with two columns. In the first column X there are numbers from 1 to 10000. The second column Y is empty. This is how I set this up:

create table test1 (
  x number,
  y number
);

begin
  for l in 1..10000 LOOP
    insert into test1(x)
    values(l);
  end loop;
  commit;
end;

My first question is: I need to use an update statement to set the values of column Y in the reverse order to those in column X, i.e. from 10000 to 1. The row with X=1 needs to have Y=10000; the row with X=2 needs to name Y=9999; etc. How can I do that?

My second question is: how can I do a math operation (SQL or PL/SQL) which will leave in the column only those numbers which can be divided to 17?

Upvotes: 1

Views: 1156

Answers (5)

BobC
BobC

Reputation: 4424

OK, if you need it in PL/SQL

begin
  update test1
  set y = 10001 - x;
end;
/
commit;

Upvotes: 1

Avrajit Roy
Avrajit Roy

Reputation: 3303

A very simple SQL query whoich will suffice your problem. Hope it helps.

MERGE INTO TEST1 USING
( SELECT X,ROW_NUMBER() over(order by 1 ASC) Y FROM TEST1 ORDER BY 1 DESC
)a ON (TEST1.X =a.X )
WHEN matched THEN
  UPDATE SET TEST1.Y = DECODE(MOD(A.Y,17),0,A.Y,NULL);

Upvotes: 1

MT0
MT0

Reputation: 168051

You do not need PL/SQL or an update statement:

INSERT INTO test1 ( x, y )
  SELECT CASE MOD( lvl, 17 )
           WHEN 0
           THEN lvl * 3 -- Your "math operation"
           ELSE lvl
           END,
         10001 - lvl
  FROM   ( SELECT LEVEL AS lvl
           FROM DUAL
           CONNECT BY LEVEL <= 10000 );

COMMIT;

Or:

INSERT INTO test1 ( x, y )
WITH cte ( lvl ) AS (
  SELECT 1 FROM DUAL
UNION ALL
  SELECT lvl + 1
  FROM   cte
  WHERE  lvl < 10000
)
SELECT CASE MOD( lvl, 17 )
         WHEN 0
         THEN lvl * 3 -- Your "math operation"
         ELSE lvl
       END,
       10001 - lvl
FROM   cte;

But if you need PL/SQL then you can just wrap it in an anonymous block:

DECLARE
  upper_bound CONSTANT NUMBER := 10000;
BEGIN
  INSERT INTO test1 ( x, y )
    SELECT CASE MOD( lvl, 17 )
             WHEN 0
             THEN lvl * 3 -- Your "math operation"
             ELSE lvl
             END,
           upper_bound + 1 - lvl
    FROM   ( SELECT LEVEL AS lvl
             FROM DUAL
             CONNECT BY LEVEL <= upper_bound );

  COMMIT;
END;
/

Upvotes: 1

JohnHC
JohnHC

Reputation: 11205

For the divisible by 17, use mod()

where mod(x,17) = 0 -- remainder (MOD) when divided by 17 = 0

Upvotes: 3

BobC
BobC

Reputation: 4424

Answer to first question:

update test1
set y = 10001 - x
;

commit;

Upvotes: 2

Related Questions