as a FK
CREATE TABLE [dbo].[Sponsor] (
[SponsorID] [int] NOT NULL ,
[SponsorCode] [char] (15) NOT NULL ,
[BrandID] [int] NOT NULL ,
[FirstDate] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sponsor] WITH NOCHECK ADD
CONSTRAINT [PK_Sponsor] PRIMARY KEY CLUSTERED
(
[SponsorID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[SponsorEvent] (
[Date] [int] NOT NULL ,
[BarbChanID] [int] NOT NULL ,
[StartTime] [int] NOT NULL ,
[AreaFlags] [smallint] NOT NULL ,
[PlatformFlags] [tinyint] NOT NULL ,
[EndTime] [int] NOT NULL ,
[SponsorID] [int] NOT NULL ,
[SponsorICodeID] [int] NOT NULL ,
[SponsorINameID] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[SponsorEvent] WITH NOCHECK ADD
CONSTRAINT [PK_SponsorEvent] PRIMARY KEY CLUSTERED
(
[Date],
[BarbChanID],
[StartTime],
[AreaFlags],
[PlatformFlags]
) WITH FILLFACTOR = 90 ON [PRIMARY]
what is wrong with this syntax
update Sponsor set FirstDate = att.[Date]
from (
SELECT SponsorID,MIN([DATE]) From SponsorEvent GROUP by SponsorID)
att,Sponsor s
where s.SponsorID = att.SponsorID
Query Analyser complains of
"No column was specified for column 2 of 'att'."
I am trying to group by SponsorID on SponsorEvent, work out what the minimum
date is on SponsorEvent and update [Date] on Sponsor with this where
SponsorID is in common.
Thanks
Stephen Howe> Query Analyser complains of
> "No column was specified for column 2 of 'att'."
Isn't it "No column NAME was specified...?"
This happens when you apply a calculation on a column and don't include an
alias. The outer query doesn't see a column named "Date", it only sees the
expression MIN([Date]), which is not the same thing.
How about :
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor
INNER JOIN
(
SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att
ON s.SponsorID = att.SponsorID
Or, better yet, instead of having to run this update every single time the
SponsorEvent changes, drop the column firstdate from the sponsor table.
There is no reason to store this redundant information when you can always
get it directly from SponsorEvent.
As an aside, I recommend renaming the column [Date]. It is never a good
idea to use reserved words as column names.
A|||Sorry, forgot the s:
> FROM Sponsor
Should be
FROM Sponsor s|||Thanks for response
> Isn't it "No column NAME was specified...?"
No. Definitely not. What I wrote was copy and pasted out of QA.
There is a message in colour red just before. The full message is
Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'att'.
> How about :
> UPDATE Sponsor
> SET FirstDate = att.FirstDate
> FROM Sponsor
> INNER JOIN
> (
> SELECT SponsorID, [Date] = MIN([DATE])
> FROM SponsorEvent
> GROUP by SponsorID
> ) Att
> ON s.SponsorID = att.SponsorID
Does not work. I changed text to
SET FirstDate = att.[Date]
FROM Sponsor s
and now I get
"Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'att' does not match with a table name or alias name used
in the query."
> Or, better yet, instead of having to run this update every single time the
> SponsorEvent changes, drop the column firstdate from the sponsor table.
> There is no reason to store this redundant information when you can always
> get it directly from SponsorEvent.
Yes you are right and I would like to.
But the reason why is that we have a analogous situation for tables where
Sponsor and SponsorEvent which have SponsorID in common
is reproduced with
SpotFilm and Spot which have SpotFilmID in common
SpotFilm and Sponsor have identical columns apart from the ID fields. It is
also such that if you concatenated them, the ID's are unique, no row in
common.
I say "somewhat symmetrical" because the difference is that data in
SponsorEvent is split between Spot and Slot.
Spot as a table has over 100 million rows (and gets larger per w
).Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDate
just happens to be a compromise. I know what you are saying, and I agree in
principal. At some point this year the server will be upgraded from SQL
Server 7 to 2000 and maybe the optimisation might be better.
> As an aside, I recommend renaming the column [Date]. It is never a good
> idea to use reserved words as column names.
I know. Noted. We have a table called [Break] which I am loathe to change.
Granted it is a keyword, but in the industry I am in "Break" is the most
descriptive word there is for the data it contains. They really are called
"Commercial Breaks". But I will change [Date] :-)
Stephen Howe|||What, do you have a case sensitive collation? Try using att everywhere and
no Att vs. att?
This works fine for me. You might want to provide similar DDL in the future
to make it easier for others to reproduce your scenario and test their
results. (See http://www.aspfaq.com/5006)
USE Tempdb
GO
CREATE TABLE Sponsor
(
SponsorID INT,
FirstDate SMALLDATETIME
)
GO
CREATE TABLE SponsorEvent
(
SponsorID INT,
[Date] SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT Sponsor SELECT 1, NULL
INSERT Sponsor SELECT 2, NULL
INSERT Sponsor SELECT 3, NULL
INSERT Sponsor SELECT 4, NULL
INSERT SponsorEvent SELECT 1, '20010501'
INSERT SponsorEvent SELECT 1, '20010601'
INSERT SponsorEvent SELECT 1, '20040801'
INSERT SponsorEvent SELECT 3, '20040701'
INSERT SponsorEvent SELECT 3, '20010601'
INSERT SponsorEvent SELECT 4, '20030801'
GO
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor s
INNER JOIN
(
SELECT SponsorID, FirstDate = MIN([Date])
FROM SponsorEvent
GROUP BY SponsorID
) att
ON s.SponsorID = att.SponsorID
GO
SELECT * FROM Sponsor
GO
DROP TABLE SponsorEvent, Sponsor
GO
On 3/12/05 12:57 PM, in article ORy87zyJFHA.2716@.TK2MSFTNGP15.phx.gbl,
"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote:
> Thanks for response
>
> No. Definitely not. What I wrote was copy and pasted out of QA.
> There is a message in colour red just before. The full message is
> Server: Msg 8155, Level 16, State 2, Line 1
> No column was specified for column 2 of 'att'.
>
> Does not work. I changed text to
> SET FirstDate = att.[Date]
> FROM Sponsor s
> and now I get
> "Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'att' does not match with a table name or alias name use
d
> in the query."
>
> Yes you are right and I would like to.
> But the reason why is that we have a analogous situation for tables where
> Sponsor and SponsorEvent which have SponsorID in common
> is reproduced with
> SpotFilm and Spot which have SpotFilmID in common
> SpotFilm and Sponsor have identical columns apart from the ID fields. It i
s
> also such that if you concatenated them, the ID's are unique, no row in
> common.
> I say "somewhat symmetrical" because the difference is that data in
> SponsorEvent is split between Spot and Slot.
> Spot as a table has over 100 million rows (and gets larger per w
).> Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
> Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDa
te
> just happens to be a compromise. I know what you are saying, and I agree i
n
> principal. At some point this year the server will be upgraded from SQL
> Server 7 to 2000 and maybe the optimisation might be better.
>
> I know. Noted. We have a table called [Break] which I am loathe to change.
> Granted it is a keyword, but in the industry I am in "Break" is the most
> descriptive word there is for the data it contains. They really are called
> "Commercial Breaks". But I will change [Date] :-)
> Stephen Howe
>
>
>|||> Sorry, forgot the s:
>
> Should be
> FROM Sponsor s
Thanks Aaaron, done it
UPDATE Sponsor
SET FirstDate = att.[Date]
FROM
(SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att INNER JOIN Sponsor s ON Att.SponsorID=s.SPonsorID|||> What, do you have a case sensitive collation? Try using att everywhere
and
> no Att vs. att?
I don't think we do. Anything to do with SQL Server 7 not recognising the
syntax?
Perhaps SQL Server 2000 is "improved" in that order does not matter
I changed the order putting Sponsor s last, (after the SELECT .. GROUP BY)
and at that point it recognised it.
Strange
But thanks
Stephen|||Date is not just a reserved word, it is too vague to be a valid data
element name -- date of what' Is there only one sponsor, as you
showed with a singular name? If you were writing SQL instead of a
strange dialect, would this be what you meant? It is a bad practice to
name relationship tables by concatenating names together -- always ask
if the relationship has its own name. Endorsements, sponsorships, etc.
UPDATE Sponsors
SET firstdate -- of what'
= (SELECT MIN(foobar_date)
FROM Endorsements AS E
WHERE E.sponsor_id = Sponsors.sponsor_id)
You might want to read a book on SQL and see what the standard UPDATE
syntax is.
And get a book on data modeling. Names like "SponsorICodeID" make
absolutley no sense. A code is not an identifier; it is a scalar value
on a nominal scale. Most of the rest of your DDL looks like you are
writing SQL with flags, etc. -- all the classic mistakes of someone who
does not know how to make a schema.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1110772877.481289.236930@.l41g2000cwc.googlegroups.com...
> It is a bad practice to name relationship tables by concatenating
names together
Mr. Celko,
Really? I've been doing a lot of that lately (sometimes M-to-M
relationships are stumpers). Is that a part of a standard I can take
a look at? Perhaps you could provide a weblink to an article or other
work with an extensive description of why it is bad, and the process
of doing better.
Sincerely,
Chris O.|||>> [name relationship tables by concatenating names together ] I've
been doing a lot of that lately (sometimes M-to-M relationships are
stumpers). <<
Not if you start from a data model. Relationships important enough to
be modeled tend to have names -- "Marriages" instead "ManWoman' or even
worse "CivilUnions" instead of "ManMan_WomanWoman_ManWomen". A
relationship name invites the attributes that go with the relationship;
Marriages implies a wedding date, license number, etc.
Google up ISO-11179; the principle is to name a thing for what it *is*,
not for what it *does* in a particular situation, not for hopw you
build it-- i.e. this is an "automobile", not a "TiresFrameMotor"; the
whole not the parts.
an extensive description of why it is bad, and the process of doing
better. <<
Look for an entire book on SQL style about the middle of this year from
me.
No comments:
Post a Comment