ewang1205
asked on
update incrementally
I have the following table with only EMP_NO populated. I like the update VALUE column like the following (10X increment for each EMP_NO). What is the simple SQL? I don't want to see a solution with 4 hard coded update statement. I like soft coded. Thanks.
EMP_NO VALUE
1 10X
3 20X
4 30X
5 40X
EMP_NO VALUE
1 10X
3 20X
4 30X
5 40X
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
update tablename set value=emp_no * 10;
ASKER
sathyagiri and abbcooper:
order by emp_no SQL command not properly ended
nav_kum_v and jwittenm: set value=emp_no * 10 won't work because there is no no relation of 10 between empno and value column. The relationship is order by emp_no.
Thanks.
order by emp_no SQL command not properly ended
nav_kum_v and jwittenm: set value=emp_no * 10 won't work because there is no no relation of 10 between empno and value column. The relationship is order by emp_no.
Thanks.
>>I like the update VALUE column like the following (10X increment for each EMP_NO)<<
In this statement, does X represent the letter X, some unknown value, or does it mean times some incremental value? If you start with the following values in your table, what do you specifically want them to be after your update?
emp_no value
1 null
3 null
4 null
5 null
In this statement, does X represent the letter X, some unknown value, or does it mean times some incremental value? If you start with the following values in your table, what do you specifically want them to be after your update?
emp_no value
1 null
3 null
4 null
5 null
In this statement, does X represent the letter X, some unknown value, or does it mean times some incremental value? If you start with the following values in your table, what do you specifically want them to be after your update?
emp_no value
1 null
3 null
4 null
5 null
I'm pretty sure he just wants the value to increment by 10, regardless of what emp_no is:
1 10
3 20
4 30
5 40
ewang1205 -- put a semi-colon at the end of your statement. Not sure what else could be the issue.
emp_no value
1 null
3 null
4 null
5 null
I'm pretty sure he just wants the value to increment by 10, regardless of what emp_no is:
1 10
3 20
4 30
5 40
ewang1205 -- put a semi-colon at the end of your statement. Not sure what else could be the issue.
ASKER
I wrote a for loop and have i =i+10. Works.
declare
i number;
cursor l12 is
select emp_no from emp;
begin
i:=10;
for l12_rec in l12 loop
i:=10;
update emp set values = i||'X' where emp_no = l12_rec.emp_no ;
i :=i+10;
end loop;
commit;
end;
/
declare
i number;
cursor l12 is
select emp_no from emp;
begin
i:=10;
for l12_rec in l12 loop
i:=10;
update emp set values = i||'X' where emp_no = l12_rec.emp_no ;
i :=i+10;
end loop;
commit;
end;
/
ASKER
Here is the working code:
declare
i number;
cursor l12 is
select emp_no from emp2 order by emp_no;
begin
i:=10;
for l12_rec in l12 loop
update emp2 set value = i||'X' where emp_no = l12_rec.emp_no ;
i :=i+10;
end loop;
commit;
end;
/
declare
i number;
cursor l12 is
select emp_no from emp2 order by emp_no;
begin
i:=10;
for l12_rec in l12 loop
update emp2 set value = i||'X' where emp_no = l12_rec.emp_no ;
i :=i+10;
end loop;
commit;
end;
/
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Since there is no relation between empno and value column, you can try any of the above methods. ( because you want 40 for empno 5 )