Updating TableA using a condition from TableB

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

Updating TableA using a condition from TableB

Author

Topic

csweets
Starting Member

2 Posts

Posted - 2010-03-02 : 12:52:13

Hello SQL Team,

I'm new to many of the more advanced aspects of T-SQL, and I'm having some trouble writing an update statement with a join.

I am trying to update TableA, only if a value in TableB is equal to 'answerChoice1' or 'answerChoice3'. The Primary Key of TableA, is a Foreign Key in TableB.

Here is what I have been trying:

update TableA a
join TableB b
on a.PrimaryKey = b.ForeignKey
set
a.Column1 = 2.5000000000,
a.Column2 = 'Adjustment',
a.column3 = 'User',
a.column4 = 1.0000000000
where
b.Value = 'answerChoice1'
and a.column5 = 'UniqueIdentifier'
or
b.Value = 'answerChoice3'
and a.column5 = 'UniqueIdentifer'

Any suggestions? I have found examples of the JOIN going after the update, after the set, and after the where on different forums online, and I'm not sure which is correct.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:54:38

[code]
update a
set
a.Column1 = 2.5000000000,
a.Column2 = 'Adjustment',
a.column3 = 'User',
a.column4 = 1.0000000000
from TableA a
join TableB b
on a.PrimaryKey = b.ForeignKey
where
(b.Value = 'answerChoice1'
or
b.Value = 'answerChoice3' )
and a.column5 = 'UniqueIdentifer'
[/code]

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

csweets
Starting Member

2 Posts

Posted - 2010-03-02 : 13:26:47

Thank you!

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-02 : 15:16:55

If is it difficult to use for you, then you can use traditional syntax of UPDATE

UPDATE A
SET a.Column1 = 2.5000000000, 
    a.Column2 = 'Adjustment', 
    a.column3 = 'User', 
    a.column4 = 1.0000000000 
FROM TableA A
WHERE EXISTS
      (SELECT *
         FROM TableB B
        WHERE A.PrimaryKey = B.ForeignKey
          AND B.Value IN ('answerChoice1', 'answerChoice3'))
  AND A.column5 = 'UniqueIdentifer'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:30:01

quote:

Originally posted by ms65g

If is it difficult to use for you, then you can use traditional syntax of UPDATE

UPDATE A
SET a.Column1 = 2.5000000000, 
    a.Column2 = 'Adjustment', 
    a.column3 = 'User', 
    a.column4 = 1.0000000000 
FROM TableA A
WHERE EXISTS
      (SELECT *
         FROM TableB B
        WHERE A.PrimaryKey = B.ForeignKey
          AND B.Value IN ('answerChoice1', 'answerChoice3'))
  AND A.column5 = 'UniqueIdentifer'

join is that difficult?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources