We help IT Professionals succeed at work.

UPDATE JOIN multiple tables

Andrew
Andrew asked
on
5 Comments
1 Solution
125 Views
Last Modified: 2017-04-19
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

View Solution Only

CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
AndrewProgrammer

Author

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

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

AndrewProgrammer

Author

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

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