update the table based on conditions


eno  ename  path              
1    aaaaaa  /ss/aaa/bbbb

 need to update the EMP table if passing input path  is not equal to the existing path for that employee id.millions of employee ids will execute this update statement in the same transaction.

can we do it using single update statement or else by using pl/sql block.i need to call this using java program.

if(<inputpathpassingparameter> <> path)
update emp set path=<inputpathpassingparameter>,name=<inputnamepassingparameter> where id=1
update emp set path=<inputpatpassingparameterh> where id=1

Open in new window

LVL 20
chaitu chaituAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:
Hi Chaituu

I am not sure whether I understood your question right

If you want to update a column only if there is any change than existing value , try something like

update <table> set <columnname> = <newvalue> where <columnname> != <newvalue>

chaitu chaituAuthor Commented:
if both are same then i need to update only one column with <newvalue>
if <columnname> = <newvalue>
update <table> set <columnname> = <newvalue> where <columnname> = <newvalue>

if both are different then i need to update the 3 columns,

update <table> set <columnname> = <newvalue> ,<column2>= <newvalue2>,<column3>= <newvalue3> where <columnname> != <newvalue>;

can't we do this with one update statement?
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
It doesn't make sense to update a column to its same value (as only operation):
update emp set path=<inputpathpassingparameter> where path =<inputpathpassingparameter>

Open in new window

The only effect is that you change a rowversion/timestamp value this way, if it exists, to reflect that the row has been changed somehow.

The traditional approach for performing a conditional update is exactly above, update with the column's value if you do not want to change it:
update emp set path = <inputpathpassingparameter>,
               name = case when path = <inputpathpassingparameter> then name
                                else <inputnamepassingparameter> end

Open in new window

However, even that doesn't make much sense. You can only pass a single path and name.

Aside from that, your chosen Topic Areas are MSSQL based, but you talk about PL/SQL blocks - which is Oracle SQL.

Please sit back for a minute, and try again to formulate your needs.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

chaitu chaituAuthor Commented:
sorry for the late reply.

I will explain my requirement once again.

current DB emp table

eno  ename  path              
1    aaaaaa  /ss/aaa/bbbb
2    bbbbb   /ss/bbb/ccc
3    xxxx    /xx/zz/eeee

3RD PARTY DB emp table

eno  ename   path              
1    CCCCC   /ss/aaa/bbbb
2    ddddd   /ss1/bbb1/ccc1
3    fffff   /rhry/fhfh/sfsf

initially i will pass the current DB values(enos- 1,2 ...) to 3rd party DB and retrieve the path and then compare the current db path and 3rd party DB emp path if both are equal then update only ename otherwise update both ename and path.
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Since you will update each row anyway ("ename"), the most simple way is to update all ename and path columns without further checking.
update emp3
set ename = empC.ename
    path = empC.path
from "3rdPartyDBemp" emp3
join "currentDBemp" empC
where emp3.eno = empC.eno

Open in new window

chaitu chaituAuthor Commented:

sorry you dont have such privilege to  join the 3rd party tables with local tables.so initially using webservice we need to get data from 3rd party tables and need to execute update on the local table  based on above conditions.
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That doesn't change much. You'll need some sort of cursor or whatever to get the 3rd-party values, and then stuff them in a stupid update:
update emp set ename = «3rdparty.ename», path = «3rdparty.path»
where emp.eno = «3rdparty.path»

Open in new window

How to get the value of the 3rd-party emp table is completely up to you, and not part of this question. The point is: You do not need any conditional update.
chaitu chaituAuthor Commented:
i am expecting this kind of query.i assume (inserted.id) will get updated id.

DECLARE @MyTableVar table (empid int);
            update emp
            path= case when path <> '3rdpartypassedpath'  then '3rdpartypassedpath' else path end,
            empname= case when path <> '3rdpartypassedpath'  then  '3rdpartypassedename' else '3rdpartypassedename' end
            OUTPUT inserted.id  INTO @MyTableVar
            where idreposi='abcd'
            --Display the result set of the table variable.
            SELECT empid FROM @MyTableVar;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaitu chaituAuthor Commented:
myself as given the exact solution for this question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.