UPDATE JOIN multiple tables

Ahelbling
Ahelbling used Ask the Experts™
on
Hello,

I am trying to update [SeedTracker].[dbo].[DBLogNonResInt].Cassava (bit datatype) from a bit value from another table [SeedTracker].[dbo].[Location].Cassava joined by DBLogNonResint.Location = Location.Description.  If the value in Location.Cassava is True then update the DBLogNonResInt.Cassava to True otherwise update to FALSE.

My latest failed attempt:

UPDATE
[SeedTracker].[dbo].[DBLogNonResInt]
SET
Cassava = 0.0160
FROM
[SeedTracker].[dbo].[DBLogNonResInt] nr
INNER JOIN
[SeedTracker].[dbo].[Location] l
ON
nr.Location = l.[Description]
WHERE
l.Cassava = 1

Open in new window

location tableDBLog table
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented: 
UPDATE [SeedTracker].[dbo].[DBLogNonResInt]
SET [SeedTracker].[dbo].[DBLogNonResInt].Cassava=t2.Cassava
FROM [SeedTracker].[dbo].[Location] as t2
WHERE [SeedTracker].[dbo].DBLogNonResint.Location = t2.Description

Open in new window

Ahelbling

Author

Commented:
Excellent!  Thank you!  I was making it much tougher than what it really was like usual.
YZlat

Commented:
sorry, forgot the "false" part. try this:

UPDATE [SeedTracker].[dbo].[DBLogNonResInt]
SET [SeedTracker].[dbo].[DBLogNonResInt].Cassava=
        (
            CASE
                WHEN
                    (t2.Cassava=True)
                THEN
                    t2.Cassave
                ELSE
                    False
            END
        )
FROM [SeedTracker].[dbo].[Location] as t2
WHERE [SeedTracker].[dbo].DBLogNonResint.Location = t2.Description

Open in new window

Ahelbling

Author

Commented:
It's ok, I got it. Thanks!
YZlat

Commented:
or

UPDATE [SeedTracker].[dbo].[DBLogNonResInt]
SET [SeedTracker].[dbo].[DBLogNonResInt].Cassava=
        (
            CASE (t2.Cassava)
                    WHEN TRUE THEN t2.Cassava
                ELSE
                    False
            END
        )
FROM [SeedTracker].[dbo].[Location] as t2
WHERE [SeedTracker].[dbo].DBLogNonResint.Location = t2.Description

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial