Update table from tabel with Where 1

[shiggyshag]

Hi

I have a table which i need to update from some datat in another table but both need to have a where clause

So I need to update Table1 where contactID = @ContactID
From Table2 Where solID = @SolID
below is what I have I just can not figure out how to selest a where in from

ALTER PROCEDURE dbo.StoredProcedure2
(@SolID int,
@ContactID nvarchar(50))
AS

UPDATE dbo.tContactSol
SET dbo.tContactSol.Sol = dbo.tPanelSol.Sol
From dbo.tPanelSol (where?)
Where contactID = @ContactID

 

[ablackwe]

See if the following works:

UPDATE dbo.tContactSol
SET dbo.tContactSol.Sol = (SELECT distinct dbo.tPanelSol.Sol
FROM dbo.tPanelSol
WHERE solID = @SolID)
Where contactID = @ContactID

Hope this helps

 

[hmckillop]

What joins the two tables, what field provides the link?
There has to be a condition where table1.field1 = table2.field2 otherwise there is way that makes sense to try and do what you are doing.
Are you tables designed ok, do you have a foreign key/ primary key in tables?



"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

Apologies having read blackwa, I eat my words (not for the 1st time I might add)

"I'm living so far beyond my income that we may almost be said to be living apart

 

[shiggyshag]

Hi

That works great but what if I need to update more that one column will I have to set each on to a select?

Cheers

 

[ablackwe]

You will need to have a seperate sub select statement for each column that you wish to update, i.e.

UPDATE dbo.tContactSol
SET dbo.tContactSol.Sol = (SELECT distinct dbo.tPanelSol.Sol
FROM dbo.tPanelSol
WHERE solID = @SolID),
<Column 2> = <New subselect statement>,
<Column 3> - <New subselect statement>
Where contactID = @ContactID