August 31, 2016 at 12:42 pm
Hi Team,
I have 3 columns to keep the history of all the action taken against the IDs likewise
Action1, Action2, Action3.
Now whenever any action taken on any ID, if the first column found Null, then the respective action should be updated in that column. I am able to manage it, but when it comes like, if Action1 is NOT NULL, update Action2 and if Action1 and Action2 is NOT NULL, update Action3. Can somebody help me to write a TSQL for this work.
Thanks,
SM
August 31, 2016 at 4:01 pm
Your database is not normalized properly. If you can, you'd be better off fixing that rather than trying to code around it.
It always helps to provide sample data in the form of DDL to create the tables and DML to insert the values into those table as outlined in the first link in my signature. You should also provide expected results based on the sample data. (It does no good if the expected results don't match the sample data.)
You also don't mention what happens if Column3 is not null. Does it get overwritten?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 1, 2016 at 6:16 am
There are only three kind of actions taken on any ID, and business wants to have all the history action that was taken on any particular ID. So here we trying to update First Action on any ID if found blank, or else same value should go to Second Action column and if both columns are not blank, the value should go to the third column.
ID 1st Action 2nd Action 3rd Action
1 Hired
2 Hired Disabled
3 Hired Disabled Deleted
Likewise.
Thanks,
SM
September 1, 2016 at 6:30 am
You should pay attention to what Drew is saying. Right now, the business rules might say that you'll only have 3 actions on each item, but later, you'll be able to have a fourth action and you'll need a fourth column. This can happen, I've seen it.
For now, until a redesign is in place, you can use CASE expressions in your updates. You'll always include the 3 columns in your update each with different conditions. The first column should only check if it's null, the following columns must check if the previous is not null and the current column is null. Try it and come back if you need more help, but remember to read the articles in my signature to get better help.
September 1, 2016 at 11:16 am
Hi,
For now I need to merge these three queries, Please let me know if there is a way to accomplish this.
UPDATE A
SET A.ACTION1=B.ACTION
FROM TABLE_A A,TABLE_B B
WHERE A.ID=B.ID AND A.ACTION1 IS NULL
UPDATE A
SET A.ACTION2=B.ACTION
FROM TABLE_A A,TABLE_B B
WHERE A.ID=B.ID AND A.ACTION1 IS NOT NULL AND A.ACTION2 IS NULL
UPDATE A
SET A.ACTION3=B.ACTION
FROM TABLE_A A,TABLE_B B
WHERE A.ID=B.ID AND A.ACTION1 IS NOT NULL AND ACTION2 IS NOT NULL
September 1, 2016 at 1:42 pm
Here's an option, you need to complete the last column.
UPDATE A
SET ACTION1= CASE WHEN A.ACTION1 IS NULL THEN B.ACTION ELSE A.ACTION1 END,
ACTION2= CASE WHEN A.ACTION1 IS NOT NULL AND A.ACTION2 IS NULL THEN B.ACTION ELSE A.ACTION2 END
FROM TABLE_A A
JOIN TABLE_B B ON A.ID=B.ID;
September 1, 2016 at 4:30 pm
Drew and Luis are correct here. Now only can it happen, but it will happen eventually. I've seen it more times than I care to count.
September 2, 2016 at 1:05 pm
I also wonder if you may want in the future to know if when those actions occurred. In your current design you would need three new columns for the times of action N. It is way cleaner to have this table broken up into
Entities
-----------
entityID
...
..
.
-----------
ActionTypes
---------------
actionTypeID
actionName
..
.
-----------
ActionTransactions
------------------------
someRowID
entityID (Foreign Key)
actionTypeID (Foreign Key)
..
.
timeStamp (a date column)
---------------------------
That is , if you have any power to alter the data structures that you are working with.
----------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply