Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Conditional SQL Update from another table

Posted on 2010-09-03
3
Medium Priority
?
377 Views
Last Modified: 2012-05-10
Conditional SQL Update from another table
 I need to update eStatus.NewL1 with the value of eLookup.L1
if (eStatus.Desc = eLookup.Desc) AND (eStatus.HomeLabor1 = eLookup.L1H)
ELSE (eStatus.Desc = eLookup.Desc) AND (eLookup.LHome IsNull)

Table eStatus
EmpKey Desc      HomeLabor1      NewL1
1                 T                    X
2                 T                    Y
3                 T                    Z
4                 U                    P

Table eLookup
Desc      L1H         L1
T                                V
T                A              D
T                X              E
T                Y              E
U               K
U                R            R

So the results should be:
Table eStatus

EmpKey Desc      HomeLabor1      NewL1
1                 T                    X                   E
2                 T                    Y                   E
3                 T                    Z                   V
4                 U                    P                   K


Thanks!
0
Comment
Question by:n2dweb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 33594499
this should do:
UPDATE s
   SET s.NewL1 = l.L1
  FROM eStatus s
  JOIN eLookup l
    ON l.Desc = s.Desc
   AND isnull(l.L1H, s.HomeLabor1 ) = s.HomeLabor1   

Open in new window

0
 
LVL 10

Expert Comment

by:joriszwaenepoel
ID: 33594502
Something like:

UPDATE [eStatus]
    SET [NewL1] = [L1]
    FROM eLookup                    
INNER JOIN eStatus ON eStatus.Desc = eLookup.Desc) AND ((eStatus.HomeLabor1 = eLookup.L1H)
                      OR (eLookup.LHome Is Null))
0
 
LVL 1

Author Closing Comment

by:n2dweb
ID: 33594518
Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question