April 14, 2014 at 1:13 pm
I am going to try and simplify this because I think I am thinking to hard for the problem at hand....
I have 2 tables Attendee and Justice
CREATE TABLE [dbo].[Attendee](
[AttendeeID] [int] IDENTITY(1,1) NOT NULL,
[AttendeeLName] [nvarchar](255) NULL,
[AttendeeFName] [nvarchar](255) NULL,
[AttendeeMName] [nvarchar](255) NULL,
[AttendeeAlias] [nvarchar](255) NULL,
[AttendeeDOB] [datetime2](7) NULL,
[JusticeID] [int] NULL,
[JusticeLName] [nvarchar](255) NOT NULL,
[OfficerID] [int] NULL,
[OfficerLname] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_Attendee] PRIMARY KEY CLUSTERED
(
[AttendeeID] 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].[Attendee] WITH CHECK ADD CONSTRAINT [FK_Justice] FOREIGN KEY([JusticeID], [JusticeLName])
REFERENCES [dbo].[Justice] ([JusticeID], [JusticeLName])
GO
ALTER TABLE [dbo].[Attendee] CHECK CONSTRAINT [FK_Justice]
GO
ALTER TABLE [dbo].[Attendee] WITH CHECK ADD CONSTRAINT [FK_Officer] FOREIGN KEY([OfficerID], [OfficerLname])
REFERENCES [dbo].[ProbationOfficer] ([OfficerID], [OfficerLName])
GO
ALTER TABLE [dbo].[Attendee] CHECK CONSTRAINT [FK_Officer]
GO
CREATE TABLE [dbo].[Justice](
[JusticeID] [int] IDENTITY(1,1) NOT NULL,
[JusticeLName] [nvarchar](255) NOT NULL,
[JusticeFName] [nchar](10) NULL,
[JusticeMName] [nchar](10) NULL,
[TownID] [int] NULL,
CONSTRAINT [pk_Justice] PRIMARY KEY CLUSTERED
(
[JusticeID] ASC,
[JusticeLName] 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].[Justice] WITH CHECK ADD CONSTRAINT [FK_Town] FOREIGN KEY([TownID])
REFERENCES [dbo].[Town] ([TownID])
GO
ALTER TABLE [dbo].[Justice] CHECK CONSTRAINT [FK_Town]
GO
I got the data for these tables from a very messy AccessDB. All I want to do (for now) is take the JusticeID from the Justice table and insert it into the Attendee table where the JusticeLName match in each table...
Right now the JusticeID in attendee is null (I added the column in as a foreign key).
I thought I was on track with something like
insert into attendee(JusticeID)
select a.JusticeID
from Justice a
join Attendee b ON a.JusticeLName = b.JusticeLName
but that obviously isn't working
Please help
April 14, 2014 at 1:28 pm
If you are updating existing data you use the UPDATE statement. INSERT is used to insert new data.
April 14, 2014 at 2:17 pm
Something like this maybe?
update a
set JusticeID = j.JusticeID
from Justice j
join Attendee a ON a.JusticeLName = b.JusticeLName
Please note that I changed your aliases. It is not a good habit to just use a, b, c for aliases. It is too hard to remember what alias goes with what table when they have no bearing. If you use short yet somewhat rational aliases you will soon find that you use the same alias for a given table just about everything you refer to it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2014 at 5:23 am
Thanks! worked like a charm 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply