Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Different Values needs to update in same cell

Author  Topic 

Ganesan
Starting Member

4 Posts

Posted - 2013-11-15 : 01:55:02
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-15 : 02:20:51
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

4 Posts

Posted - 2013-11-15 : 02:48:51
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-15 : 05:09:10
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

52326 Posts

Posted - 2013-11-15 : 06:08:14
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

4 Posts

Posted - 2013-11-17 : 21:15:58
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
   

- Advertisement -