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
 General SQL Server Forums
 New to SQL Server Programming
 UPDATE A TABLE USING ANOTHER TABLE???		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sonia_newbie
Starting Member

India
17 Posts
Posted - 03/07/2013 :  00:59:20  Show Profile  Reply with Quote
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

India
47040 Posts
Posted - 03/07/2013 :  01:02:50  Show Profile  Reply with Quote 

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


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

russell
Pyro-ma-ni-yak

USA
4965 Posts
Posted - 03/07/2013 :  01:04:09  Show Profile  Visit russell's Homepage  Reply with Quote 

UPDATE	c
SET	P_id = p.P_id
FROM	child as c
JOIN	parent as p
On	p.name = c.name


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
Edited by - russell on 03/07/2013 01:04:49
Go to Top of Page

ahmeds08
Constraint Violating Yak Guru

India
424 Posts
Posted - 03/07/2013 :  01:09:12  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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

India
17 Posts
Posted - 03/10/2013 :  00:46:02  Show Profile  Reply with Quote
thanks all !! it worked!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 03/10/2013 :  04:08:34  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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.08 seconds. Powered By: Snitz Forums 2000