How to update table based on the contents of other table
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
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
Opps, I didn't read the question correctly. ignore that response.
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?
Does any yellow make it yellow if there is no red?
The only way it can be green is if all are green?
ASKER
That is correct that is exactly what I need. Thanks
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'
Your ELSE for the CASE will of course be 'green'
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.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
acperkins:
thanks. i am getting this error "Invalid Column name DayColor"
thanks. i am getting this error "Invalid Column name DayColor"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
update query
update tableB set dayColor = t.color from (Select [date],
case Left(Main.Colour,Len(Main.
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]
update tableB set dayColor = t.color from (Select [date],
case Left(Main.Colour,Len(Main.
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]
>>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.
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.
ASKER
thx
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?
FROM TableA
INNER JOIN TableB ON TableA.Date = TableB.Date