Jump to content

Pl/sql Doubt

sanbk

By sanbk
in Old Discussions

 Share

Recommended Posts

kedharinath Sr. Hero

kedharinath

Posted
Posted

Reseed the data

 

 

DRL use cheyali emo... 

 

 

ltt for pl-sql experts..

 

in sql server nenu series of delete insert identity set chestanu..

 

 

 

update statement tho value set cheyaleva??

 

like update table name set column name value = 2 ani?

 

 

Data Retrival Language man... monna ma school lo chepparu ma madam. INSERT, UPDATE, DELETE options untai andhulo 

gallery_8818_6_385253.gif?1367349476gallery_8818_6_385253.gif?1367349476

  • Replies 46
  • Created
  • Last Reply

Top Posters In This Topic

  • kedharinath

    10

  • sanbk

    9

  • mtkr

    8

  • loveindia

    8

Popular Days

Top Posters In This Topic

Popular Days

pythonic Celebrity

pythonic

Posted
Posted

boiling bablu... asalu requirement enti... motham column A anta 1 badulu 2 raavali antenaaa...

 

if so

 

UPDATE tablename

SET ColumnA = 2

WHERE columnA = 1

 

nee requirement naaku antey ardam aindi.... explain more if you need more...

 

BEGIN

 

UPDATE tablename

SET ColumnA = 2

WHERE columnA = 1;

 

END;

 

PL/SQL ayithe edhi...gallery_8818_6_385253.gif?1367349476

 

 

@TS uncle PL/SQL lo you can write sql squaments which will in turn gets executed in SQL engine...

loveindia LEGEND

loveindia

Posted
Posted

plz explain logging in sql server thrd... :3D_Smiles_38:

 

logging in sql server or ssis man??? separate thread eskundam man... amav26.gif?1368598550

loveindia LEGEND

loveindia

Posted
Posted

BEGIN

 

UPDATE tablename

SET ColumnA = 2

WHERE columnA = 1;

 

END;

 

PL/SQL ayithe edhi...gallery_8818_6_385253.gif?1367349476

 

 

@TS uncle PL/SQL lo you can write sql squaments which will in turn gets executed in SQL engine...

 

nuvvu raasina daaniki nenu raasinaa daaniki difference enti man... Begin End ; aaa... amav26.gif?1368598550

powerstar02 LEGEND

powerstar02

Posted
Posted

loveindia edhi chusina andulo difference chustadu

loveindia LEGEND

loveindia

Posted
Posted

loveindia emi rasina different ga raastadu

 

loveindia edhi chusina andulo difference chustadu

 

endhi anniyaa ee landasura posts.... amav26.gif?1368598550

sanbk Hero

sanbk

Posted
  • Author
Posted

i guess ne req upd colA with frst available val frm colB anukuntaa...

 

leakpoithe direct updt stmt eseiii... :5_2_108:

 

You are correct bro.. Update col A with first available from Col B everytime.. repeat aayyinapudu prathi sari

sanbk Hero

sanbk

Posted
  • Author
Posted

Column A        Column B

 

1                          55

1                          45

1                          79

1                          64

1                          89

2                          23

2                          87

3                          45

4                          123

4                          111

4                          65

5                           72

5                          31

5                          18

5                          05

 

Ila unnai bro.. so 1st should be replaced to 55

 

1- 55

2-23

3-45

4-123

 

chepandi bhayya koncham ippudu

mtkr LEGEND

mtkr

Posted
Posted 
drop table updvalues

create table updvalues
(
col1 varchar(20),
col2 varchar(20)
)


insert into updvalues values('1','50')
insert into updvalues values('1','53')
insert into updvalues values('1','56')
insert into updvalues values('1','58')
insert into updvalues values('2','60')
insert into updvalues values('2','63')
insert into updvalues values('2','66')
insert into updvalues values('3','70')
insert into updvalues values('3','72')
insert into updvalues values('3','76')
insert into updvalues values('3','78')


select * from updvalues

bayya exact gaa oracle lo ela raayalo telavadam ledhu... rownum() tho cheyyochu...

 

e query will produce ur result..

select *
from 
(
select col1, col2, row_number() over (partition by col1 order by col1) as seqnum 
from updvalues
)a
inner join 
(
select col1, col2
from updvalues
)b
on a.col1 = b.col1
and a.col2 = b.col2
and a.seqnum = 1

so ee paina query in update statment to raayaalii..

somethng lky

 

ikkada nen 1 ane value in pass chestuunaa...

update updvalues 
set col1 = (select a.col2 
from 
(
select col1, col2, row_number() over (partition by col1 order by col1) as seqnum 
from updvalues
)a
inner join 
(
select col1, col2
from updvalues
)b
on a.col1 = b.col1
and a.col2 = b.col2
and a.seqnum = 1
and a.col1 = 1
)
where col1 = 1

so cursor or loop lo above query in petti aa 1 value in variable thru pass cheyyalii...

 

 

assuming ne frst value in col is 1 and nxt vals are in sequential order....

 

so sometng lyk ilaa undaalii...


declare value int := 1;
max_value int := (select max(col1) from updvalues;
begin

 loop
 
 WHILE :value <= :max_value
 
update updvalues 
set col1 = (select a.col2 
from 
(
select col1, col2, row_number() over (partition by col1 order by col1) as seqnum 
from updvalues
)a
inner join 
(
select col1, col2
from updvalues
)b
on a.col1 = b.col1
and a.col2 = b.col2
and a.seqnum = 1
and a.col1 = :value
)
where col1 = :value;

SET :value = :value + 1;

END loop;

some syntax errors unnai.. oracle standard ki adjust cheskoo...

 

sanbk Hero

sanbk

Posted
  • Author
Posted 
drop table updvalues

create table updvalues
(
col1 varchar(20),
col2 varchar(20)
)


insert into updvalues values('1','50')
insert into updvalues values('1','53')
insert into updvalues values('1','56')
insert into updvalues values('1','58')
insert into updvalues values('2','60')
insert into updvalues values('2','63')
insert into updvalues values('2','66')
insert into updvalues values('3','70')
insert into updvalues values('3','72')
insert into updvalues values('3','76')
insert into updvalues values('3','78')


select * from updvalues

bayya exact gaa oracle lo ela raayalo telavadam ledhu... rownum() tho cheyyochu...

 

e query will produce ur result..

select *
from 
(
select col1, col2, row_number() over (partition by col1 order by col1) as seqnum 
from updvalues
)a
inner join 
(
select col1, col2
from updvalues
)b
on a.col1 = b.col1
and a.col2 = b.col2
and a.seqnum = 1

so ee paina query in update statment to raayaalii..

somethng lky

 

ikkada nen 1 ane value in pass chestuunaa...

update updvalues 
set col1 = (select a.col2 
from 
(
select col1, col2, row_number() over (partition by col1 order by col1) as seqnum 
from updvalues
)a
inner join 
(
select col1, col2
from updvalues
)b
on a.col1 = b.col1
and a.col2 = b.col2
and a.seqnum = 1
and a.col1 = 1
)
where col1 = 1

so cursor or loop lo above query in petti aa 1 value in variable thru pass cheyyalii...

 

 

assuming ne frst value in col is 1 and nxt vals are in sequential order....

 

so sometng lyk ilaa undaalii...


declare value int := 1;
max_value int := (select max(col1) from updvalues;
begin

 loop
 
 WHILE :value <= :max_value
 
update updvalues 
set col1 = (select a.col2 
from 
(
select col1, col2, row_number() over (partition by col1 order by col1) as seqnum 
from updvalues
)a
inner join 
(
select col1, col2
from updvalues
)b
on a.col1 = b.col1
and a.col2 = b.col2
and a.seqnum = 1
and a.col1 = :value
)
where col1 = :value;

SET :value = :value + 1;

END loop;

some syntax errors unnai.. oracle standard ki adjust cheskoo...

 

 

Thx bro.. I will check it

sanbk Hero

sanbk

Posted
  • Author
Posted 
drop table updvalues

create table updvalues
(
col1 varchar(20),
col2 varchar(20)
)


insert into updvalues values('1','50')
insert into updvalues values('1','53')
insert into updvalues values('1','56')
insert into updvalues values('1','58')
insert into updvalues values('2','60')
insert into updvalues values('2','63')
insert into updvalues values('2','66')
insert into updvalues values('3','70')
insert into updvalues values('3','72')
insert into updvalues values('3','76')
insert into updvalues values('3','78')


select * from updvalues

bayya exact gaa oracle lo ela raayalo telavadam ledhu... rownum() tho cheyyochu...

 

e query will produce ur result..

select *
from 
(
select col1, col2, row_number() over (partition by col1 order by col1) as seqnum 
from updvalues
)a
inner join 
(
select col1, col2
from updvalues
)b
on a.col1 = b.col1
and a.col2 = b.col2
and a.seqnum = 1

so ee paina query in update statment to raayaalii..

somethng lky

 

ikkada nen 1 ane value in pass chestuunaa...

update updvalues 
set col1 = (select a.col2 
from 
(
select col1, col2, row_number() over (partition by col1 order by col1) as seqnum 
from updvalues
)a
inner join 
(
select col1, col2
from updvalues
)b
on a.col1 = b.col1
and a.col2 = b.col2
and a.seqnum = 1
and a.col1 = 1
)
where col1 = 1

so cursor or loop lo above query in petti aa 1 value in variable thru pass cheyyalii...

 

 

assuming ne frst value in col is 1 and nxt vals are in sequential order....

 

so sometng lyk ilaa undaalii...


declare value int := 1;
max_value int := (select max(col1) from updvalues;
begin

 loop
 
 WHILE :value <= :max_value
 
update updvalues 
set col1 = (select a.col2 
from 
(
select col1, col2, row_number() over (partition by col1 order by col1) as seqnum 
from updvalues
)a
inner join 
(
select col1, col2
from updvalues
)b
on a.col1 = b.col1
and a.col2 = b.col2
and a.seqnum = 1
and a.col1 = :value
)
where col1 = :value;

SET :value = :value + 1;

END loop;

some syntax errors unnai.. oracle standard ki adjust cheskoo...

 

 

actually nenu kuda update stmt rasanu kaani.. ikkada meeku example laaga ala columns cheppa.. but real scenario.. 2 diff. tables and normal update not working.. throwing error.. Cursor use chesi try chesta.. update emanna work avutadi emo

kedharinath Sr. Hero

kedharinath

Posted
Posted

loveindia emi rasina different ga raastadu

antha different ga emundhi..

 

antha sinnavi kuda rakunda job lu ela sesthunarra ayya..gallery_8818_6_385253.gif?1367349476

mtkr LEGEND

mtkr

Posted
Posted

actually nenu kuda update stmt rasanu kaani.. ikkada meeku example laaga ala columns cheppa.. but real scenario.. 2 diff. tables and normal update not working.. throwing error.. Cursor use chesi try chesta.. update emanna work avutadi emo


2 diff tbls ante join cheyyalevaa two tbls ni?? no comon col a??
 Share
Go to topic listing
×
×
  • Create New...