Updating a table with another table's contents.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating a table with another table's contents.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-05 : 14:53:41

I used MJV's Date function to create the following table:

CREATE TABLE [tblRedFridayAllDates] (
	[DATE_ID] [int] NOT NULL ,
	[DATE] [datetime] NOT NULL ,
	[YEAR] [smallint] NOT NULL ,
	[MONTH] [tinyint] NOT NULL ,
	[DAY_OF_WEEK] [tinyint] NOT NULL ,
	[REDFRIDAY] [bit] NULL ,
	[Period] [tinyint] NULL ,
	[YearPeriod] [bigint] NULL ,
	CONSTRAINT [PK__tblRedFridayAllD__30F848ED] PRIMARY KEY  CLUSTERED 
	(
		[DATE]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

The bolded part is my addition. I have another table created from the actual table from my erp database.

CREATE TABLE [tblRedFriOnly] (
	[REDFRIDAY] [datetime] NOT NULL ,
	[SATURDAY] [datetime] NOT NULL ,
	[FISCAL_YEAR] [int] NULL ,
	[PERIOD] [smallint] NULL ,
	[SalesForecast] [money] NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[REDFRIDAY]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

I updated table 1 from table 2 to add red fridays with the following:

update redfridaydates.reports.tblredfridayalldates
set redfriday = 1
where date in (select redfriday from redfridaydates.reports.tblredfrionly)

What I'm now trying to do is update the periods from table 2 to table 1. They look like months (numbers 1 through 12) but are based on what we call Red Fridays which are roughly a month apart.

So, as I see it, I need to do an update statement on table 1 that says something like:

update Period in table one from Period in table 2 corresponding to the dates and which period they fall in.

How can I do this? I hope I've explained this properly.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-05 : 15:05:55

Actually, this works, but is there a better way?

UPDATE redfridaydates.reports.tblredfridayalldates
SET     Period =
        (
                SELECT  redfridaydates.reports.tblredfrionly.period
                FROM    redfridaydates.reports.tblredfrionly
                WHERE   redfridaydates.reports.tblredfridayalldates.Date > =redfridaydates.reports.tblredfrionly.saturday
                    AND redfridaydates.reports.tblredfridayalldates.Date < = redfridaydates.reports.tblredfrionly.redfriday
        )

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-09-05 : 16:21:21

May be this? But I dont think there will be any performance improvement. You can check and see.


UPDATE redfridaydates.reports.tblredfridayalldates
SET     Period = redfridaydates.reports.tblredfrionly.period
FROM 
	redfridaydates.reports.tblredfrionly , 
	redfridaydates.reports.tblredfridayalldates
WHERE 
	redfridaydates.reports.tblredfridayalldates.Date > =redfridaydates.reports.tblredfrionly.saturday
AND redfridaydates.reports.tblredfridayalldates.Date < = redfridaydates.reports.tblredfrionly.redfriday

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources