SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Different Values needs to update in same cell
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ganesan
Starting Member

India
4 Posts

Posted - 11/15/2013 :  01:55:02  Show Profile  Reply with Quote
Hi experts,

I'm New to SQL server 2005 (EVEN DATABASE QUERIES),I need a help on the same.
I have the Update command to Update the "phone number" into a table.
User May change the phone number many times, i would like to make a record of what are the phone numbers updated by the User.

USERID PHONE NUMBER

0001 01234562,01542252,01254545

FROM THE ABOVE TABLE THE USER UPDATED HIS PHONE NUMBER 3 TIMES AS ABOVE. SAME PHONE NUMBERS NEED NOT BE UPDATE.

IF THIS IS NOT A VALID POINT, THEN I CAN HAVE 3 COLUMNS LIKE
PHONE NUMBER1, PHONE NUMBER2, PHONE NUMBER3, WHEN FIRST TIME UPDATE COMMAND EXECUTES UPDATE IN THE FIRST COLUMN, THEN SECOND AND SO ON..
THIS WAY ALSO APPRECIATED.
HOW CAN WE ACHIEVE THIS? PLEASE HELP ME TO SOLVE THIS ISSUE.

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/15/2013 :  02:20:51  Show Profile  Reply with Quote
Check the follwoing approach

1) Tables
Users Table with all details UserId, UserName and PhoneNumber
UserPhoneHistory Table with UserId, PhoneNumbers 

Users 
UserId, UserName, PhoneNumber
1        chandu    6768974

UserPhoneHistory 
UserId, PhoneNumbers 
1        3234712
1        4542352

2) Whenever user wants to update Phone number then insert that deleted record into USerPhoneHistory table ( either use TRIGGER or Stored Procedure )

Trigger for UPDATE

CREATE TRIGGER Tr_UserPhoneUpdate 
ON Users 
FOR UPDATE
AS 
IF ( UPDATE (PhoneNumber))
BEGIN
   INSERT UserPhoneHistory (UserId, PhoneNumbers )
   SELECT d.UserId, d.PhoneNumber
   FROM deleted d 
   JOIN inserted i ON i.UserId = d.UserId
   WHERE i.PhoneNumber NOT IN (SELECT u.PhoneNumbers FROM UserPhoneHistory u WHERE u.UserId = d.UserId )
END

NOTE: Also check for phone Number duplication 


--
Chandu
Go to Top of Page

Ganesan
Starting Member

India
4 Posts

Posted - 11/15/2013 :  02:48:51  Show Profile  Reply with Quote
Thank you Chandu for your quick reply.
I'm working in Industrial Automation Domain, So, it's somewhat tough to understood, I have an idea, but i don't know how to make as SQL Query.

I have 3 column as i said earlier, first time execution needs to execute in first column and so on, IS that possible, because that is easy to me to implement in my project.

I seen a command called "coalesce", this can tell us which column is free? from here can we do something on which column the current data needs to update?

Thanks
Ganesan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/15/2013 :  05:09:10  Show Profile  Reply with Quote
am not getting your point.. can you tell us the exact structure which you are following?
Post us back the table structure and asample data... then expected output with explanation

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/15/2013 :  06:08:14  Show Profile  Reply with Quote
quote:
Originally posted by Ganesan

Thank you Chandu for your quick reply.
I'm working in Industrial Automation Domain, So, it's somewhat tough to understood, I have an idea, but i don't know how to make as SQL Query.

I have 3 column as i said earlier, first time execution needs to execute in first column and so on, IS that possible, because that is easy to me to implement in my project.

I seen a command called "coalesce", this can tell us which column is free? from here can we do something on which column the current data needs to update?

Thanks
Ganesan


storing phonenumbers in concatnated format is not a good idea expecially when you want to capture their updates. You may be better off storing them like this

USERID PHONENUMBER EFFDate

0001 01234562 Date1
0001 01542252 Date2
0001 01254545 Date3

where each date denotes datefrom which new number is effective

Once you make atable like this its easier to capture the new additions and updates using a simple trigger like

CREATE  TRIGGER TriggerName
ON Users
FOR UPDATE
AS
BEGIN
INSERT UserPhoneHistory
SELECT d.*
FROM INSERTED i
JOIN DELETED d
ON d.UserID = i.USERID
WHERE i.PHONENO <> d.PHONENO
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ganesan
Starting Member

India
4 Posts

Posted - 11/17/2013 :  21:15:58  Show Profile  Reply with Quote
Sorry Chandu for the late reply..
The Requirement is to keep record of maximum 3 updates of phone numbers from each user. customer needs a table like below.

ID PHONENUMBER1 PHONENUMBER2 PHONENUMBER3

The above table would be update not insert operation. The actual full table looks like this

ID NAME LOGINDATE LOGOUTDATE PHONENUMBER1 PHONENUMBER2 PHONENUMBER3

When User Logged in, I will insert the Name and login time, when user logs out, that time i will update the log out and how many times the user changed his phone numbers where the ID number is " ". Like you said above, i already did primary key on the ID column, so duplicate ID can't possible.



Thanks
Ganesan
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000