April 20, 2011 at 9:00 pm
Hi Guys:
I want to update a table with values from the same table. What I want to do is Fill StreetNumber, StreetName, StreetType with the ones where company is the same and suburb is the same and postcode is the same and state is the same.
Before:
CompanyName StreetNumber StreeName StreetType Suburb Postcode State
123 Acc Brisbane 4000 QLD
123 Acc 123 Pitt Street Brisbane 4000 QLD
Expected Result After:
CompanyName StreetNumber StreeName StreetType Suburb Postcode State
123 Acc 123 Pitt Street Brisbane 4000 QLD
123 Acc 123 Pitt Street Brisbane 4000 QLD
I need this because I am going to run a query to select the distinct rows, erase the contents of the table and then re insert them into the location table.
Please note that CompanyName is part of Company Table and the others are part from location table.
Thanks in advance,
Martin
April 21, 2011 at 12:31 am
Hi Martin
Please can you post CREATE TABLE statements for both tables. Some sample data for each would be awesome. Also, the simple query which returns:
CompanyName, StreetNumber, StreeName, StreetType, Suburb, Postcode, State
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 21, 2011 at 1:45 am
Hi Chris:
-------------------------COMPANY TABLE------------------------------------------------------
USE [VisitationDb]
GO
/****** Object: Table [dbo].[Company] Script Date: 04/21/2011 17:39:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Company](
[CompanyId] [int] IDENTITY(0,1) NOT NULL,
[CompanyName] [nvarchar](150) NULL,
[Website] [nvarchar](200) NULL,
[IndustryId] [int] NULL,
[ApplicabilityId] [int] NULL,
[OverridingAgent] [nchar](60) NULL,
[Priority] [nchar](60) NULL,
[Franchise] [nchar](3) NULL,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[CompanyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Company] WITH CHECK ADD CONSTRAINT [FK_Company_Industry] FOREIGN KEY([IndustryId])
REFERENCES [dbo].[Industry] ([IndustryId])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Company] CHECK CONSTRAINT [FK_Company_Industry]
GO
--------------------------------------LOCATION TABLE----------------------------------------------
USE [VisitationDb]
GO
/****** Object: Table [dbo].[Location] Script Date: 04/21/2011 17:40:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Location](
[LocationId] [int] IDENTITY(0,1) NOT NULL,
[StreetNumber] [nchar](100) NULL,
[StreetName] [nchar](100) NULL,
[StreetType] [nchar](100) NULL,
[Suburb] [nvarchar](50) NULL,
[Postcode] [nchar](4) NULL,
[State] [nchar](3) NULL,
[SwitchPh] [nchar](10) NULL,
[CompanyId] [int] NULL,
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[LocationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Location] WITH CHECK ADD CONSTRAINT [FK_Location_Company] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Company] ([CompanyId])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Location] CHECK CONSTRAINT [FK_Location_Company]
GO
-------------------------------QUERY-------------------------------------------------------------
SELECT Company.CompanyName, Location.StreetNumber, Location.StreetName, Location.StreetType, Location.Suburb, Location.Postcode, Location.State
FROM Company INNER JOIN
Location ON Company.CompanyId = Location.CompanyId
----------------------------------------------------------------------------------------------------
Thanks Chris
April 21, 2011 at 3:48 am
Also, INSERT statements to create data in both tables which will generate this result:
CompanyName StreetNumber StreeName StreetType Suburb Postcode State
123 Acc Brisbane 4000 QLD
123 Acc 123 Pitt Street Brisbane 4000 QLD
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 21, 2011 at 8:33 am
I don't understand what you mean.
Please explain.
Thanks
April 21, 2011 at 9:38 am
Hi Martin, sorry for not making it sufficiently clear. What I'm after is data in both tables, and your query, to replicate your findings. So...
INSERT INTO Company ([columnlist]) VALUES ([Valuelist])
INSERT INTO Location ([columnlist]) VALUES ([Valuelist])
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 21, 2011 at 4:12 pm
Sorry Chris but I don't expect this result.
"Also, INSERT statements to create data in both tables which will generate this result:
CompanyName StreetNumber StreeName StreetType Suburb Postcode State
123 Acc Brisbane 4000 QLD
123 Acc 123 Pitt Street Brisbane 4000 QLD"
I am expecting:
CompanyName StreetNumber StreeName StreetType Suburb Postcode State
123 Acc 123 Pitt Street Brisbane 4000 QLD
123 Acc 123 Pitt Street Brisbane 4000 QLD
All I need to do is fill the gaps where streetnumber, streetname and streettype are.
For me the use of a insert statement doesn't make sense because I am not going to add new records. All I am planning to do is update the previously mentioned gaps with the data from the other fields.
Thanks,
Martin
April 22, 2011 at 12:00 am
Hi Chris I just tried the insert statement and modify the one that I previously had and It worked.
Thanks for your help.
Martin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply