• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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

chaitu chaitu
chaitu chaitu
  • 5
  • 3
1 Solution
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 and DeveloperCommented:
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.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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 and DeveloperCommented:
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 and DeveloperCommented:
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;
chaitu chaituAuthor Commented:
myself as given the exact solution for this question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now