UPDATE Problem - 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 2005 Forums

Transact-SQL (2005)

UPDATE Problem

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-06-11 : 20:15:12

I need to update a single table based on the information from another table. Here's what I mean:

insTable has 2 columns :
newINS INT
oldINS INT

AssessTable has 1 column:
ID

Here's my pseudo code:
IF insTable.newINS <> insTable.oldINS
THEN AssessTable.ID = insTable.newINS
ELSE do nothing

It may sound simple enough to do, but I can't figure it out. I'd rather not use a cursor either. Help!!

--Nick

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-11 : 20:23:18

sample data and expected result please

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-06-11 : 21:05:13

Ok, like a few rows would look like this:

insTable | AssessTable
old new | ID
---- ---- | ----------
107 107 | 107
107 107 | 107
107 109 | 109
107 109 | 109
. . | .
. . | .
. . | .
so if the old/new are different in the insTable then update the AssessTable with the newINS value.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-11 : 21:23:09

quote:
IF insTable.newINS <> insTable.oldINS
THEN AssessTable.ID = insTable.newINS

You want to update AssessTable's ID to newINS ? What is the relationship between AssessTable and insTable ?

is it AssessTable.ID = insTable.oldIns ?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-12 : 00:06:02

two table columns have duplicate entries... You have to normalise the table

--------------------------------------------------
S.Ahamed

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 09:14:04

All you need is simple select. Do you need seperate table to have this?

Select new from table
where old<>new

Madhivanan

Failing to plan is Planning to fail

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-06-12 : 16:27:37

Ok, sorry for the lack of information. My fault, I mislabeled some things and it came out completely wrong. Let me start over.

I have 1 table called AssessTable Primary Key is: ID

I have a view called viewIns this view selects things from other irrelevant tables and the main things it selects are ID, newIns, and oldIns

The ID value in viewIns is the Primary Key in the AssessTable, so use that to link/update.
Now, I need a way to look at the newIns and oldIns values in viewIns and compare them like this:

IF (newIns <> oldIns)
THEN update AssessTable SET AssessTable.ID = insView.newINS WHERE AssessTable.ID = viewIns.ID
ELSE do nothing

expected results:
insTable | AssessTable
old new | ID
---- ---- | ----------
107 107 | 107
107 107 | 107
107 109 | 109
107 109 | 109
. . | .
. . | .
. . | .

--Nick

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 19:49:48

quote:
IF (newIns <> oldIns)
THEN update AssessTable SET AssessTable.ID = insView.newINS WHERE AssessTable.ID = viewIns.ID
ELSE do nothing

the viewIns.ID is newINS or oldINS ?

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-06-13 : 15:20:38

The viewIns.ID is the Primary Key in the AssessTable, namely AssessTable.ID

so JOIN ON (viewIns.ID = AssessTable.ID)

--Nick

dxsacjason
Starting Member

2 Posts

Posted - 2007-06-14 : 15:07:11

yak.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 21:08:42

actually you already got the query there

update a
SET    ID = i.newINS 
FROM   AssessTable a INNER JOIN viewIns i
ON     viewIns.ID = AssessTable.ID)
WHERE  i.newIns <> i.oldIns

dxsacjason
Starting Member

2 Posts

Posted - 2007-06-19 : 18:25:39

quote:
Originally posted by khtan

actually you already got the query there

update a
SET ID = i.newINS
FROM AssessTable a INNER JOIN viewIns i
ON viewIns.ID = AssessTable.ID)
WHERE i.newIns <> i.oldIns

KH

Thank you khtan, that worked!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources