September 29, 2011 at 8:22 am
I struggled with this for a while and I know I am not the only one. So, since I have received help from others here at the forum, I thought I would give back. There are no problems with this script, that I am aware of, and hence don't need any answers. I DO, however, welcome comments or questions about how to make this even better or learn about other tweaks or variation. As it stands, this query creates all the tables, INSERTS data into and SELECTS from them. It also begins a transaction, UPDATES the table, SELECTS the tables again to see the update actually took place. Then it rolls back the transaction and displays again the table to show that the formerly NULL columns are NULL again due to the ROLLBACK. Then, the script cleans itself up by dropping the tables. This can be copied and pasted right into SSMS or Query Analyzer and run right there on the spot. You may need to change the USE statement at the top and further down to use another database. Make sure you run the whole script at once. Otherwise you may not get the expected results.
USE [Sandbox]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Projects]') AND type in (N'U'))
DROP TABLE [dbo].[Projects]
GO
CREATE TABLE [dbo].[Projects](
[ProjectID] [int] NOT NULL,
[ProjectName] [varchar](100) NULL,
[ProjectDescription] [varchar](100) NULL
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Components]') AND type in (N'U'))
DROP TABLE [dbo].[Components]
GO
CREATE TABLE [dbo].[Components](
[ComponentID] [int] NOT NULL,
[ComponentName] [varchar](100) NULL,
[ProjectID] [int] NULL,
[ComponentDescription] [varchar](100) NULL,
[ProjectName] [varchar](100) NULL,
[ProjectDescription] [varchar](100) NULL
) ON [PRIMARY]
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[Projects]([ProjectID], [ProjectName], [ProjectDescription])
SELECT 1, N'Project1', N'ProjectDesc1' UNION ALL
SELECT 2, N'Project2', N'ProjectDesc2' UNION ALL
SELECT 3, N'Project3', N'ProjectDesc3'
COMMIT;
RAISERROR (N'[dbo].[Projects]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[Components]([ComponentID], [ComponentName], [ProjectID], [ComponentDescription], [ProjectName])
SELECT 101, N'ComponentName101', 1, N'ComponentDesc101', NULL UNION ALL
SELECT 102, N'ComponentName102', 2, N'ComponentDesc102', NULL UNION ALL
SELECT 103, N'ComponentName103', 3, N'ComponentDesc103', NULL UNION ALL
SELECT 104, N'ComponentName104', 2, N'ComponentDesc104', NULL UNION ALL
SELECT 105, N'ComponentName105', 1, N'ComponentDesc105', NULL
COMMIT;
RAISERROR (N'[dbo].[Components]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
USE [Sandbox]
GO
BEGIN TRANSACTION
SELECT Components.ComponentID [C.ComponentID]
, Components.ComponentName [C.ComponentName]
, Components.ComponentDescription [C.ComponentDescription]
, Components.ProjectID [C.ProjectID]
, Components.ProjectName [C.ProjectName]
, Components.ProjectDescription [C.ProjectDescription]
, Projects.ProjectID [P.ProjectID]
, Projects.ProjectName [P.ProjectName]
, Projects.ProjectDescription [P.ProjectDescription]
FROM Components
INNER JOIN Projects
ON Components.ProjectID = Projects.ProjectID
ORDER BY ComponentID
UPDATE Components
SET Components.ProjectName = Projects.ProjectName,
Components.ProjectDescription = Projects.ProjectDescription
FROM Components
INNER JOIN Projects
ON Components.ProjectID = Projects.ProjectID
SELECT Components.ComponentID [C.ComponentID]
, Components.ComponentName [C.ComponentName]
, Components.ComponentDescription [C.ComponentDescription]
, Components.ProjectID [C.ProjectID]
, Components.ProjectName [C.ProjectName]
, Components.ProjectDescription [C.ProjectDescription]
, Projects.ProjectID [P.ProjectID]
, Projects.ProjectName [P.ProjectName]
, Projects.ProjectDescription [P.ProjectDescription]
FROM Components
INNER JOIN Projects
ON Components.ProjectID = Projects.ProjectID
ORDER BY ComponentID
ROLLBACK
SELECT Components.ComponentID [C.ComponentID]
, Components.ComponentName [C.ComponentName]
, Components.ComponentDescription [C.ComponentDescription]
, Components.ProjectID [C.ProjectID]
, Components.ProjectName [C.ProjectName]
, Components.ProjectDescription [C.ProjectDescription]
, Projects.ProjectID [P.ProjectID]
, Projects.ProjectName [P.ProjectName]
, Projects.ProjectDescription [P.ProjectDescription]
FROM Components
INNER JOIN Projects
ON Components.ProjectID = Projects.ProjectID
ORDER BY ComponentID
GO
DROP TABLE Components
DROP TABLE Projects
GO
Hope this helps somebody. Thank you all for help you have given me.
September 29, 2011 at 9:32 am
Hi there. Glad you've had help on the forum and thanks for sharing.
So, is the purpose of this to highlight the UPDATE...FROM syntax?
That is a perfectly valid way of doing this (and prior to SQL 2008 was the only practical way). As you asked for comments, I'd point out that there is a new way in SQL 2008 which is also in the ANSI Standard (UPDATE...FROM is proprietary, which some people care about more than others :-)). If you look at the code below, it's an alternative way to do the same thing:
MERGE dbo.Components AS target
USING dbo.Projects AS Source
ON ( Source.ProjectID = target.ProjectID )
WHEN MATCHED
THEN UPDATE
SET ProjectName = source.ProjectName ,
ProjectDescription = Source.ProjectDescription;
If you want to read up more on it, have a look on BOL:
September 29, 2011 at 10:52 am
Thank you. I like this. I have seen it before, but forgotten about it. Actually my purpose was just to make an easy way to review Update syntax. If this was proprietary, I didn't even know it. Now I wonder how the syntax would go on other databases (such as Oracle, MySQL, etc.) I haven't done that many Updates lately, and when I got one to work, I thought maybe someone else (probably a beginner) could benefit from a complete, scripted solution instead of struggling through Google and piecing together things like creating the table, then inserting rows into it, etc. I know that has taken me a lot of extra time sometimes when I really just wanted to understand it right away. I also thought maybe the comments that are made would help me understand a few other ideas, and yours did.
September 29, 2011 at 11:26 am
The way most RDBMSs do this is with a sub-query, or Merge.
UPDATE dbo.MyTable
SET MyColumn1 =
(SELECT MyColumnA
FROM dbo.MyOtherTable
WHERE MyOtherTable.ColumnB = MyTable.Column2) ;
- 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
October 4, 2011 at 5:31 am
Hi , Thanks for sharing so important comcept .
But can't we perform join in this way becz in the output result we are getting colum names like
(C.Componentname or P.projectname ) to avoid this we can go with below query.
Thank you for the insformation ..
SELECT C.ComponentID, C.ComponentName, C.ComponentDescription, C.ProjectID, C.ProjectName,C.ProjectDescription,
P.ProjectID, P.ProjectName, P.ProjectDescription FROM dbo.Components C
INNER JOIN dbo.Projects P
ON C.ProjectID = P.ProjectID
ORDER BY C.ComponentID
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply