Simple UPDATE?? - 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 2000 Forums

Transact-SQL (2000)

Simple UPDATE??

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2008-08-26 : 09:12:51

Hi all,

I am trying to update a column in a source table based on data from the destination.

I have two tables tableA(Source) with 800+rows the other table has 170 rows.

I need to search tableA with the 170 rows from tableB(holding), simple join. but i also need to update the rows in tableA with data from tableB.

the only way i can di it is by using a fixed value:

UPDATE a
SET Matched = 100
FROM tblSource AS a JOIN
HoldingtableFULLMatch AS b ON a.[Row ID] = b.[Row ID]
WHERE a.Matched IS NULL

Kind Regards

Pete.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-26 : 09:18:34

just change the value to a column name in table HoldingtableFULLMatch


UPDATE a
SET    Matched = b.somecol
FROM   tblSource AS a 
JOIN   HoldingtableFULLMatch AS b 
ON     a.[Row ID] = b.[Row ID]
WHERE  a.Matched IS NULL

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

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-26 : 09:18:39

May be you are looking for this,

UPDATE a
SET Matched = b.YourColumn
FROM tblSource AS a JOIN
HoldingtableFULLMatch AS b ON a.[Row ID] = b.[Row ID]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-26 : 09:22:51

I I understand you right...

You've got 2 tables (a and b), a had a direct relationship to b (by rowId), you want to update the value in a with the value in b?

surely...


UPDATE a
SET
	[Matched] = b.[<dataColumn>]
FROM
	tblSource AS a
	JOIN HoldingtableFULLMatch AS b ON a.[Row ID] = b.[Row ID]
WHERE
	a.[Matched] IS NULL

Oh wait -- others are way ahead of me.......

-------------
Charlie

petek
Posting Yak Master

192 Posts

Posted - 2008-08-26 : 09:37:12

All,

thanks for the replies PROBLEM SOLVED with you help.

Kind Regards

Pete.

petek
Posting Yak Master

192 Posts

Posted - 2008-08-26 : 09:42:14

hi me again,

with this sorted I have come across another opportunity.....the data from the results are stored in another table.

But i need to make sure the row does not already exist in the destination table, any ideas??

i tried if exists but failed miserably

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-26 : 09:47:11

Use LEFT JOIN with an IS NULL check.
Or try NOT EXISTS ()...

E 12°55'05.25"
N 56°04'39.16"

petek
Posting Yak Master

192 Posts

Posted - 2008-08-27 : 03:54:49

thanks all code worked a treat.

Kind Regards

Pete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources