April 2, 2009 at 7:54 am
Hi,
I have 2 tables, 1 table contains a field that I want to update in a query of another table that will return more then 1 result.
TABLE1
store PriorAudits
----- ----------
1212 NULL
1333 NULL
4434 NULL
TABLE2
store Audit
----- -----
4434 3/14/08
4434 7/1/09
1333 5/12/07
1212 1/1/09
Not sure if clear: I have a field in my store table called 'PriorAudits", now I want to update that field while I query another table (which also has the store id) for all "priorAudits" for each store. Some stores have had more then 1 audit. I would like to end up with a "PriorAudits" field with both of those Audits, maybe seperated bya comma or a space. So the field endsup looking like this:
PriorAudits
----------
3/14/08,7/1/09
I need a start on this - do I use cursors? or a subquery? Please help, thank you
April 2, 2009 at 8:09 am
Generally, I wouldn't create the PriorAudits table at all. I'd just query the Audits table and use that. Why keep the same data in two places?
Given that, here's a way to concatenate them in a query.
-- Create sample data
if object_id(N'tempdb..#T') is not null
drop table #T;
create table #T (
ID int identity primary key,
Store int,
Audit datetime);
insert into #T (Store, Audit)
select 4434, '3/14/08' union all
select 4434, '7/1/09' union all
select 1333, '5/12/07' union all
select 1212, '1/1/09';
-- Concatenation Query
select distinct
Store,
stuff( -- Gets rid of leading comma
(select ', ' + convert(varchar(100), Audit, 1) -- No column name gets rid of that part in the XML
from #T t2
where t2.Store = t1.Store
order by Audit
for xml
path(''), -- ('') gets rid of row tags
TYPE).value('.[1]', 'varchar(MAX)') ,1,2,'') -- value query allows XML characters
from
#T t1;
This assumes you're using SQL 2005, based on the forum you posted in. If you're using 2000, it requires a different solution. 2008 can also use this version, of course.
Again, I'd use that query in a view or inline function, rather than in a table. That way, you don't have to worry about synchronizing the data between the two tables, and all the complexity that can create.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 8:39 am
Thank you,
That is an awesome query, I will work on trying to understand it - never even heard of "stuff"
I understand your concerns on the 2 tables however I don't have a choice on that one
April 2, 2009 at 8:50 am
errr...
That query gives output exactly what I was looking for...
what I can't understand is how to use the results of "no column name" as the updated field (prior_audits) in the TABLE1. Heck, it looks like TABLE1 isn't even involved in the query
I am sure I can do it as a second step - maybe that is the best way - but is there a way to do this in the one query?
If not, I am fine with it as a second step
April 2, 2009 at 8:55 am
Add a column alias after the sub-query. Would go after the "'varchar(MAX)') ,1,2,'')" part, between that and the comment. That'll give you a column name you can use to update Table1 from.
Stuff is a string function that inserts one string value into another. In this case, it "stuffs" a zero-length-string into the beginning of the thing, and replaces what would otherwise be a leading comma. Take out "stuff(" from the beginning, and ",1,2,'')" from the end, and you'll see what it's removing.
You're correct that Table1 isn't necessary for what I wrote.
As for updating Table1, are you familiar with "Update From"? Where you use one table to update another.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:12 am
OK Thanks
I'll google and learn about "update from"
I assume you are saying yes, it can be done in one query (the "update from" being the first part of the query...)
Thank you for such quick and accurate help.
April 2, 2009 at 9:20 am
--- Create sample data
if object_id(N'tempdb..#T') is not null
drop table #T;
if object_id(N'tempdb..#T1') is not null
drop table #T1;
--
create table #T ( -- Table2
ID int identity primary key,
Store int,
Audit datetime);
--
insert into #T (Store, Audit)
select 4434, '3/14/08' union all
select 4434, '7/1/09' union all
select 1333, '5/12/07' union all
select 1212, '1/1/09';
--
create table #T1 ( -- Table1
ID int identity primary key,
Store int,
PriorAudits varchar(max));
--
insert into #T1 (Store)
select distinct Store
from #T;
--
-- Update From
;with Concatenated (Store, Audits) as -- "Common Table Expression"
(select distinct
Store,
stuff( --- Gets rid of leading comma
(select ', ' + convert(varchar(100), Audit, 1) --- No column name gets rid of that part in the XML
from #T t2
where t2.Store = t1.Store
order by Audit
for xml
path(''), --- ('') gets rid of row tags
TYPE).value('.[1]', 'varchar(MAX)') ,1,2,'') --- value query allows XML characters
from
#T t1)
update #T1 -- Update
set PriorAudits = Audits
from Concatenated -- From
where #T1.Store = Concatenated.Store;
--
select *
from #T1;
The Update From is the last bit after the concatenation. That part's the same as what I had before, I just turned it into a "CTE" (Common Table Expression), so that I could use it in the From part.
Does that help? I imagine you have more questions about how it works, so go ahead and fire away on them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:35 am
Wow,
So, since I already have my tables, and I want to create just the query... do I start with your statement here:
;with Concatenated (Store, Audits) as --- "Common Table Expression"
Foreign ground with me - would I actualy start a query with ";with"?
April 2, 2009 at 9:56 am
kevinwinters (4/2/2009)
Foreign ground with me - would I actualy start a query with ";with"?
Yup, you would. It's the syntax for a common table expression (CTE), essentially a temporary view. The ; is there because a CTE requires that the previous statement be terminated with a ;. since it's not common for people to do that in SQL, the unofficial convention is to start the ctw with ;WITH ...
Check Books Online if you want more info on the CTEs, or check this out:http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 9:57 am
"With" is how you start a CTE, so yes, you would. Since it has to have a semicolon to end the command before it, I've gotten myself into the habit of starting them with one. There are only a few places where that's not applicable, but it is in this case.
A CTE (Common Table Expression) is a query that you'll use in place of a table in the later query. They can come in quite handy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 10:45 am
Incredible,
The update query worked perfectly, now to spend the next couple of hours studying why...
Thank you very much, it scares me that I would never have been able to come up with this on my own...... and that it took you about 5 minutes....
April 3, 2009 at 7:36 am
Don't let it scare you. I've been doing this kind of thing for a long time.
Definitely a good idea to work out how it does what it does. That's how I learned. Let us know if you need any help figuring it out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply