Solved

How to update table based on the contents of other table

Posted on 2011-09-21
14
227 Views
Last Modified: 2012-05-12
I want to update the color of TableB based on the the colors of TableA FOR EACH DAY.  Here are the different cases: Case1 If any given day we have all the 3 colors (Green, Yellow, Red) then we say that day is Red.   Case2: if we have any given day (Green and Yellow only) then we say that day is Yellow.   Case3: If we have any given day (Only Green) then we say that day is Green.  Here is an example that i have:

TableA
Date                                      Prdouct                          color
2011-09-19                              JJ                                Green
2011-09-19                             XL                               Green
2011-09-19                             MM                                Red
2011-09-18                              JJ                                Green
2011-09-18                             XL                               Green
2011-09-18                            MM                                Yellow
2011-09-17                              JJ                                Green
2011-09-17                             XL                               Green
2011-09-17                            MM                                Green

so i want to update the below table based on TablA

TableB
Date                    DayColor
2011-09-19            Red
2011-09-18            Yellow
2011-09-17              Green

thanks
0
Comment
Question by:karinos57
[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
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 36577171
UPDATE TableA SET Color = TableB.DayColor
FROM TableA
INNER JOIN TableB ON TableA.Date = TableB.Date
0
 
LVL 18

Expert Comment

by:lludden
ID: 36577181
Opps, I didn't read the question correctly.  ignore that response.
0
 
LVL 18

Expert Comment

by:lludden
ID: 36577196
Does any red make the day red (2011-09-19 has 2 green and 1 red)?

Does any yellow make it yellow if there is no red?

The only way it can be green is if all are green?


0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 

Author Comment

by:karinos57
ID: 36577403
That is correct that is exactly what I need.  Thanks
0
 
LVL 14

Expert Comment

by:mds-cos
ID: 36577497
Sorry, got to jump so I can't write the query for you right now.  But one fairly simple way should be to use CASE in your update query.  Syntax here:  http://www.devx.com/tips/Tip/15633

Your ELSE for the CASE will of course be 'green'
0
 
LVL 14

Expert Comment

by:mds-cos
ID: 36577528
Oh...ps....you will need to incorporate a subquery to "gather" all results together for a day.  Sorry I don't have time to really work it out right now, but first blush I'm thinking use a case in the subquery that will assign each color a numerical value.  You can then ORDER the subquery in color priority (e.g. red, yellow, green) to get highest priority on top.  Then pull the TOP 1 result from your subquery to get the color for the day.  Now you just need to use a CASE for the update to convert returned number back to text color.
0
 

Author Comment

by:karinos57
ID: 36577637
i have tried to use Case but got some issues that is why i posted here to get EE help.  Is there anyone who can help me here?  thanks
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 36577776
Try it this way:
UPDATE  tb
SET     DayColor = CASE tb.ColorIndex
                     WHEN 3 THEN 'Red'
                     WHEN 2 THEN 'Green'
                     WHEN 1 THEN 'Yellow'
                   END
FROM    TableA ta
        INNER JOIN (SELECT  [Date],
                            MAX(CASE Color
                                  WHEN 'Red' THEN 3
                                  WHEN 'Green' THEN 2
                                  WHEN 'Yellow' THEN 1
                                  ELSE NULL
                                END) ColorIndex
                    FROM    TableA
                    GROUP BY [Date]
                   ) tb ON ta.[Date] = tb.[Date]
WHERE   tb.ColorIndex IS NOT NULL

Open in new window

0
 

Author Comment

by:karinos57
ID: 36578091
acperkins:
thanks.  i am getting this error "Invalid Column name DayColor"
0
 
LVL 9

Assisted Solution

by:sachinpatil10d
sachinpatil10d earned 250 total points
ID: 36578718
Try this

Select [date],
       case Left(Main.Colour,Len(Main.Colour)-1) when 'Green' then 'Green'
       when 'Green,Yellow' then 'Yellow'
       when 'Green,Red,Yellow' then 'Red' end
       As color
From(Select distinct date,
           (Select color + ',' AS [text()]
            From tableA tA
            Where tA.date = tB.date
            group by color,[date]
            ORDER BY tA.[date],color
            For XML PATH ('')) [Colour]
     From tablea tB) [Main]
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36578730
update query

update tableB set dayColor = t.color from (Select [date],
       case Left(Main.Colour,Len(Main.Colour)-1) when 'Green' then 'Green'
       when 'Green,Yellow' then 'Yellow'
       when 'Green,Red,Yellow' then 'Red' end
       As color
From(Select distinct date,
           (Select color + ',' AS [text()]
            From tableA tA
            Where tA.date = tB.date
            group by color,[date]
            ORDER BY tA.[date],color
            For XML PATH ('')) [Colour]
     From tablea tB) [Main]
     ) t
left outer join tableB on tableB.[date] = t.[date]
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36580146
>>i am getting this error "Invalid Column name DayColor" <<
Unfortunately, we cannot go by anything more than what you post.  This is what you gave us:

I want to update the color of TableB based on the the colors of TableA FOR EACH DAY.
...
TableA
Date                                      Prdouct                          color
...
TableB
Date                   DayColor


If you are not prepared to post the real table schema we have to use what you gave us and it is up to you to translate.  We cannot give you a complete solution if you only give us half the problem.
0
 

Author Closing Comment

by:karinos57
ID: 36896881
thx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36896929
What would it have taken to get an "A" grade?  Was there something I could have done better?  Did you want me to remote into your site and type the query for you?
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Split string into 3 separate fields 5 35
When to use a Unique Index? A Clustered Index? 5 76
Need multiple Group By's 8 52
2008 to 2016 jump (SQL Server) 1 30
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

732 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