Sql Question

[sqlhelp]

hi all 

 

sql server lo  okate column lo nunchi different values ela select cheyachu ?

 

 

for eg  my table is like this 

 

name                 fieldvalue           location

 

hyd                      200                  road 

chennai               300                  bus

city                      401                  train 

city                      401                  year 

hyd                      200                 busy

pune                    510                 metro

usa                      250                 fly 

pune                    510                 map

 

 

location column  lo unna values up date chedam ani  plan   but anni fieldvalues ki kadhu  like we want to keep old data for   few  fieldvalues  like  hyd   pune   ala 

 

so my question for a basic select  ki first  can i do like this 

 

select * from table

where fieldvalue is not null  and fieldvalue <> '200' and fieldvalue <> '510'

 

or not in   use cheyacha?    table peddaga undi  so nenu ela confirm cheskovalo telitledhu  that ee values naa  selection  lo levu

 

please help

 thank you 

 

 

 

[Bairagi From Bapatla]

nu ala update cheyali ante either fieldvalue or name meedha base ayyi update cheyali so req ento yee field value unnavatini update cheyalo kanuko or else its very hard to update randomly...

[nandamuribidda]

rank caluclate chesi rowid use chesi update cheyyi.

 

not sure it will work 100% but you can give it a shot

 

ee case lo rowid corresponding to the rank vastadi kada then you can update the rows based on the row id and rank.

 

ee table ki primary key ledu kada so I thought creating some skey or unique key will help you isolate the records. 

 

[electionszindabad]

latest field ela decide chestavu? Date field lekapothe rank function use chesi update cheyyi when rank= 2 or when rank >1. some thing like that... 

[sqlhelp]

latest field ela decide chestavu? Date field lekapothe rank function use chesi update cheyyi when rank= 2 or when rank >1. some thing like that... 

 

em specific fieldvalues update cheyalo list undi  so simple ga ila ankuntuna 

 

declare 

@row int = 1 

 

while @row > 0 

update table 

set location = 'rest'

where fieldvalue not in (200,510,.. )

set @row = @@rowcount

[electionszindabad]

em specific fieldvalues update cheyalo list undi  so simple ga ila ankuntuna 

 

declare 

@row int = 1 

 

while @row > 0 

update table 

set location = 'rest'

where fieldvalue not in (200,510,.. )

set @row = @@rowcount

yeah that will work...

so ee updates only one time updates aa? or future lo ilanti requests malli ravacha? future lo ilanti requests malli vaste kanuka its better to add a " current " indicator fields or ' Latest date' fields or some sort of indicator fields to identify whether the record is current or latest.. ( basically SCD type 2  but in your case its not a full blown scd type 2 implementation but a brute force approach as explained above)

[andhravodu]

em specific fieldvalues update cheyalo list undi so simple ga ila ankuntuna

declare
@row int = 1

while @row > 0
update table
set location = 'rest'
where fieldvalue not in (200,510,.. )
set @row = @@rowcount

 

Disadvantage entante you are doing row by row, so performance down. Sql language power bulk sets meeda vadataniki, idi slow approach

 

Edit: inkokati entante, ee query whole table ni update chestundi, and repeats by number of rows enni unte. incorrect approach. at the minimum where clause emi ledu filter cheyataniki

[electionszindabad]

Disadvantage entante you are doing row by row, so performance down. Sql language power bulk sets meeda vadataniki, idi slow approach

good point

 

If the updates are few hundred records on a million+ record table then it should still be fine to do it in one go.

if there are more updates then we can do so in batches so that log files wont be filled up completely , crashing the server.

[andhravodu]

hi all 

 

sql server lo  okate column lo nunchi different values ela select cheyachu ?

 

 

for eg  my table is like this 

 

name                 fieldvalue           location

 

hyd                      200                  road 

chennai               300                  bus

city                      401                  train 

city                      401                  year 

hyd                      200                 busy

pune                    510                 metro

usa                      250                 fly 

pune                    510                 map

 

 

location column  lo unna values up date chedam ani  plan   but anni fieldvalues ki kadhu  like we want to keep old data for   few  fieldvalues  like  hyd   pune   ala 

 

so my question for a basic select  ki first  can i do like this 

 

select * from table

where fieldvalue is not null  and fieldvalue <> '200' and fieldvalue <> '510'

 

or not in   use cheyacha?    table peddaga undi  so nenu ela confirm cheskovalo telitledhu  that ee values naa  selection  lo levu

 

please help

 thank you 

 

not in is an exclusion. nuvvu first vadindi inclusion approach

 

neeku filtering cheyataniki ekkuva values unte, use IN clause

 

indaka example pedite, only Chennai, usa thappa, migata emi vaddu neeku, then use  where fieldvalue in('chennai', 'usa')

 

Filtering thakkuva values ante reverse. chennai, usa thappa migata anta kavalante use where fieldvalue not in('chennai','usa')

[andhravodu]

good point

 

If the updates are few hundred records on a million+ record table then it should still be fine to do it in one go.

if there are more updates then we can do so in batches so that log files wont be filled up completely , crashing the server.

 

millions or more update cheyataniki, batch update kante, temp table/insert into operation better

[loveindia]

em specific fieldvalues update cheyalo list undi so simple ga ila ankuntuna

declare
@row int = 1

while @row > 0
update table
set location = 'rest'
where fieldvalue not in (200,510,.. )
set @row = @@rowcount

This is bad man... u will end up in infinite loop...

[loveindia]

Disadvantage entante you are doing row by row, so performance down. Sql language power bulk sets meeda vadataniki, idi slow approach

Edit: inkokati entante, ee query whole table ni update chestundi, and repeats by number of rows enni unte. incorrect approach. at the minimum where clause emi ledu filter cheyataniki

ROFL man.. okasari query chudu malli... row by row enduku jarugutundi...

[sqlhelp]

Disadvantage entante you are doing row by row, so performance down. Sql language power bulk sets meeda vadataniki, idi slow approach

 

Edit: inkokati entante, ee query whole table ni update chestundi, and repeats by number of rows enni unte. incorrect approach. at the minimum where clause emi ledu filter cheyataniki

naku idhi kuda doubt undindi if it will hit performance  motham update ayaka  indexes rebuild chedam ankuntna  , db ni sh rink chesi  ? is this good to do ? 

 

 

where cluase lo inkooka option add chesa   that location column is not null  but   donno if it helps as idhe column lo nunchi data update cheshtuna  inka vere fields levu  to include in where  condition other than fieldvalue 

 

 between  batches lagane chedam ankuntna  and idi one time update  

[loveindia]

Put your field values in a table variable or temp table...

Use not exists clause and write a straight forward update statement man..

[loveindia]

millions or more update cheyataniki, batch update kante, temp table/insert into operation better

again Rofl man..