Update table1 based on value in table2

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

Update table1 based on value in table2

Author

Topic

StarMan
Starting Member

2 Posts

Posted - 2010-05-28 : 16:36:50

Hi,
not that I am lazy, but I can't find a proper solution for this specific problem.

IN SQL SERVER 2005 I want to update FieldA in table AAA with the value of FIELB in table BBB ONLY when FIELDC in BBB = 'true' (boolean)

Without the FIELDC = TRUE I used this code (and this works fine)

Update AAA
SET FieldA = (SELECT FieldB FROM BBB
WHERE Field_ID = AAA.FIELD_ID)
FROM TABLEA AAA

Now, with the restriction that the update can happen when FIELDC in BBB has the boolean value 'YES' I am stuck!!!!

Example (from SQL SERVER 2005, giving an error):
UPDATE AAA
SET FieldA =
(SELECT FieldB
FROM BBB
WHERE (Field_ID = AAA.Field_ID)
AND (FieldC = 'TRUE'))

THIS does not work...........

I've done the same in ACCESS 2007 and this works:

Update AAA
INNER JOIN BBB
ON AAA.Field_ID = BBB.Field_ID
Set AAA.FieldA = BBB.FieldB
Where BBB.FieldC = True

ANYONE? ANYONE, please?

Thanks in advance,

Paul

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-28 : 16:41:33

Update AAA
Set FieldA = BBB.FieldB
FROM AAA
JOIN BBB
ON AAA.Field_ID = BBB.Field_ID
Where BBB.FieldC = 'True' <-- or maybe you want FieldC = 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources