T-Sql Help - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

T-Sql Help

Author

Topic

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-11 : 19:30:01

Hi guys,
Here is my sample tableA and sample TableB
TableA
ID,STATE,PIN
1,CA,123
2,GA,456
3,MA,123

TableB
ID,PHONE,PIN
NULL,760,123
NULL,867,456,
NULL,912,123

I want to update MY ID, Here is my query

UPDATE TableB
SET TableB.id = TableA.ID
FROM TableB
INNER JOIN TableA
ON TableA.PIN = TableB.PIN

I am this result
ID,PHONE,PIN
1,760,123
2,867,456,
1,912,123

In TableB ID is Primary Key,

I want this result

TableB
ID,PHONE,PIN
1,760,123
2,867,456,
3,912,123

Appreciate your help.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-11 : 19:47:05

Not really sure about this one..seems like you want the PIN to have their own distinct ID if they are by themselves, then use that ID in the other table..


DECLARE @TableA Table (ID int not null,STATE char(2) not null,PIN int not null)
INSERT INTO @TableA
SELECT  1,'CA',123 UNION
SELECT  2,'GA',456 UNION
SELECT  3,'MA',123

Declare @TableB Table(ID int null,Phone int not null, PIN  int not null)
INSERT INTO @TableB
SELECT NULL,760,123 UNION
SELECT NULL,867,456 UNION
SELECT NULL,912,123


;WITH cteTableA AS 
	( SELECT   PIN
			,RN = ROW_NUMBER() OVER(   ORDER BY PIN )
	 FROM @TableA
	 GROUP BY PIN
	)
UPDATE @TableB
SET ID = b.RN
FROM @TableB a inner join cteTableA b on a.PIN = b.PIN

SELECT * FROM @TableB

OR, you could use the minimum ID from TableA


 


;WITH cteTableA AS 
	( SELECT   PIN
			   ,ID = MIN(ID)
	 FROM @TableA
	 GROUP BY PIN
	)
UPDATE @TableB
SET ID = b.ID
FROM @TableB a inner join cteTableA b on a.PIN = b.PIN

SELECT * FROM @TableB

Poor planning on your part does not constitute an emergency on my part.

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-11 : 20:37:55

I really appreciate your quick reply, Could you please explain your code. I tried but didn't work out.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-11 : 21:14:28

I have tried to understand why it didn't work,but since it worked for your sample data and demonstrated a possible solution...you might need to clarify what you mean by "it didn't work out"

Poor planning on your part does not constitute an emergency on my part.

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-11 : 21:49:12

Did you receive this result

TableB
ID,PHONE,PIN
1,760,123
2,867,456,
3,912,123

?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-11 : 22:20:10

Doh! I reversed what you had wanted and what you started with..! LOL

Sorry..

What is the ordering criteria?

How do you differentiate PIN 123 = ID 1 and PIN 123 = ID 3 . I presume there is other data, and you can't simply join on ID obviously.

Clarify the rule you want to be applied..

based on what you present, I can only assume "phone" is the ordering column on Table B


DECLARE @TableA Table (ID int not null,STATE char(2) not null,PIN int not null)
INSERT INTO @TableA
SELECT  1,'CA',123 UNION
SELECT  2,'GA',456 UNION
SELECT  1,'MA',123

Declare @TableB Table(ID int   null,Phone int not null, PIN  int not null)
 INSERT INTO @TableB
SELECT NULL,760,123 UNION
SELECT NULL,867,456 UNION
SELECT NULL,912,123


 ;WITH cteTableB AS 
	( SELECT   
                iD = ROW_NUMBER() OVER( ORDER BY Phone,Pin)
			   ,Phone
			   ,PIN 
	 FROM  @TableB b 
	 
	  
	)
 Update @TableB
 SET ID = b.ID
 FROM cteTableB b inner join @TableB a on b.Phone = a.PHone and b.PIN = a.PIN


SELECT * FROM @TableB

Poor planning on your part does not constitute an emergency on my part.

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-11 : 23:21:50

It was just a sample data, Let me explain you what i want, I have to create a link between tableA and tableB. There is only one column that has same in tableA and tableB is PIN, and ID is auto generate and its unique but PIN could be duplicate, There is other data in TableA.
Please let me know if you need more info. I really want to solve this problem. Thank you for your time.
Just quick question, How about can we insert ID,PIN from TableA to TableB?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-11 : 23:51:11

first how could a primary key of a table be dependent over another table ... i think not a good design.

secondly, in your desired output what is the update criteria of the table B ID column, when its duplicated ... e.g.

ID,PHONE,PIN
1,760,123
2,867,456,
3,912,123

can be manipulated as below too unless you provide some more specific details about the logic behind this sequence ..

ID,PHONE,PIN
1,912,123
2,867,456,
3,760,123

Cheers
MIK

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-12 : 00:25:15

I am transferring data from client data to our database. The logic is Linking between two table TableA and TableB. Only PIN is a common field. If you have other logic to link two table Let me know. But i can't change my company DB.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-12 : 01:52:15

how did the data in tableA & tableB comes about ?

will both table always have the same number of records ?

for the same PIN, will both table have the same number of records ?

KH
[spoiler]Time is always against us[/spoiler]

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-12 : 21:34:37

YES tableA and Table B has Same total number of records and same PIN, There is the way to create link between TableA and TableB. So ID in both tables are PRIMARY KEY.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-02-12 : 23:06:37

Try something like this:


WITH TableAWithRowNumCTE AS
(
   SELECT A.ID, A.PIN, ROW_NUMBER() OVER(PARTITION BY A.PIN ORDER BY A.ID) AS row_num
     FROM TableA AS A
)
, TableBWithRowNumCTE AS
(
   SELECT B.ID, B.PIN, ROW_NUMBER() OVER(PARTITION BY B.PIN ORDER BY B.ID /* change this if required */) AS row_num
     FROM TableB AS B
)
UPDATE B
   SET B.ID = A.ID
  FROM TableAWithRowNumCTE AS A
       INNER JOIN
       TableBWithRowNumCTE AS B
       ON B.PIN = A.PIN
          AND B.row_num = A.row_num;

But MIK notice about arbitrary ordering still holds here. So either this is fine by you, or you have to specify the ordering of TableB row_num according to your needs (you can for example change B.ID to B.PHONE if it is your ordering column).

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-12 : 23:17:20

How can TABLE B ID be the primary key if it is currently NULL as in your sample?

How can TABLE A ID be the primary key if two of the ID's are the same (based on the PIN). I think your information is incorrect.

I have one basket with oranges and apples. I have another with bananas and apples. I need to get the value of strawberries based on how many apples there are? But each basket has the same number of apples, so it is completely logical.

Why wouldn't you simply transfer the client data "as is" to your database? why the need for a DIFFERENT primary key? this sounds like an absolute mess of a scenario.

Poor planning on your part does not constitute an emergency on my part.

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-13 : 23:15:41

Thanks you all of you guys. I am using SSIS to transfer from Client server to Our company DB. My SSIS package first dropping PK. and transfer from one location to other. Let me correct my self. In TableA, ID is P.K and in TableB B_ID is P.K instead of ID. I hope now everything is clear. Malpashaa let me try your query and I will you guys. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources