lisha
asked on
how to do a look up and update...
i have a table where i need to look up .... and then update a single field..
table A
c1 c2 c3 c4 c5
table B
c1 c2 c6 c7 c8 c9
in these two we have c1 and c2 common ....
will populate c9 based on c4 if (c1,c2 are same and c4 not null)
else update c9 to value Base for c7 equal B
or update c9 to Dollar for c7 equal D
or update c9 to Sed for c7 equal S
my query does this
if c9 not null then
check in table A for C4 not null (c1 and c2 found)
else set based on c7 ...it works
but when an update happens to table and c7 gets set then it does not reflect ....
how to solve this ?
table A
c1 c2 c3 c4 c5
table B
c1 c2 c6 c7 c8 c9
in these two we have c1 and c2 common ....
will populate c9 based on c4 if (c1,c2 are same and c4 not null)
else update c9 to value Base for c7 equal B
or update c9 to Dollar for c7 equal D
or update c9 to Sed for c7 equal S
my query does this
if c9 not null then
check in table A for C4 not null (c1 and c2 found)
else set based on c7 ...it works
but when an update happens to table and c7 gets set then it does not reflect ....
how to solve this ?
Can we start with posting your original query, obfuscated if necessary? It's a little difficult to debug without seeing your joins.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the scenario is totally correct ....but here is where i am stuck ...if i update it this way it will also update the last updated date column ..right? ..as update will always happen ...even if no change in the values ..every time it runs... which we don't require.... so how to manage that ..as a trigger exists on the last updated date column on every insert/update ...
The UPDATE did happen. Trying to decide at the time of the update if it changed any values is really outside the scope of the update statement itself.
I suppose you can probably come up with SQL that will check in the where clause and only return rows where the values are different, then perform an update based on those rows but it likely wouldn't be very efficient.
A much simpler solution would be to change the trigger to check the NEW and OLD values to determine if a change happened and update a date column appropriately. I mean, you already have the trigger so you aren't introducing extra complexity.
I suppose you can probably come up with SQL that will check in the where clause and only return rows where the values are different, then perform an update based on those rows but it likely wouldn't be very efficient.
A much simpler solution would be to change the trigger to check the NEW and OLD values to determine if a change happened and update a date column appropriately. I mean, you already have the trigger so you aren't introducing extra complexity.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I believe the concepts for the last few posts from HainKurt will do what it is you want.
The reason for this post is there are some syntax issues you or him will need to correct. They won't run as-is.
The reason for this post is there are some syntax issues you or him will need to correct. They won't run as-is.
ASKER
thanks a lot .... it was becoming bothersome to check every column for the update..but you both have really helped me with all this solutions....finally can close this one to :)