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
 General SQL Server Forums
 New to SQL Server Programming
 UPDATE A TABLE USING ANOTHER TABLE???

Author  Topic 

sonia_newbie
Starting Member

17 Posts

Posted - 2013-03-07 : 00:59:20
hI

I have 2 tables names named parent and child.

parent:

name P_id details
data0878 45 jillian
data0881 85 lugo
data0867 83 cardoza


child
name P_id model
data0878 45 +1
data0878 45 -1
data0878 45 +1
data0878 45 -1
data0867 83 -1
data0867 83 +1
data0867 83 +1
data0867 83 -1
data0867 83 +1
data0867 83 -1

i want to update the table child by looking up table parent

(basically I want to update P_id of child by replacing it with P_id of parent comparing the name (as a parameter in both tables.)

Is this command correct?

UPDATE child FROM parent

P_id= 45

where name = data0878

I amnot sure whether this is correct


what should i try?

In the example i have pasted in table child , when name = data0878 the pid is same as in the parent
but i want to check whether the P_id for a particular name remains the same as parent.


am soo confused..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 01:02:50
[code]
UPDATE c
SET c.P_id = p.P_Id
FROM Child c
JOIN Parent p
ON p.[name] = c.[name]
WHERE p.P_id <> c.P_id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-07 : 01:04:09
[code]
UPDATE c
SET P_id = p.P_id
FROM child as c
JOIN parent as p
On p.name = c.name[/code]

Have a look at these:
http://msdn.microsoft.com/en-us/library/ms190014(v=sql.105).aspx
http://www.w3schools.com/sql/sql_join_inner.asp
http://msdn.microsoft.com/en-us/library/ms177523.aspx


Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-07 : 01:09:12
Is this you are looking for:

update child set p_id=p.p_id from parent p
inner join child c
on c.p_id=p.p_id
where c.name=p.name
Go to Top of Page

sonia_newbie
Starting Member

17 Posts

Posted - 2013-03-10 : 00:46:02
thanks all !! it worked!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-10 : 04:08:34
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -