Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Update a table from select statement results Expand / Collapse
Author
Message
Posted Monday, January 7, 2008 12:16 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:42 AM
Points: 1,130, Visits: 487
Happy New Year everyone!

Please bare with me, I am not very T-SQL savy. I have an update that I'm trying to do to a table that is not working. When I run the select statement to verify what I want updated, I get 1 row returned:

select cast([Fiscal Year] as varchar(4)),
cast([Fiscal Period] as varchar(2)),
cast([Date YYYYMMDD] as varchar(8))
from dim_calendar
where dim_calendar.[date] = (select max([invoice date]) from dbo.customer_salesanalysis) and (dim_calendar.division = '')
go

result: 2008 10 20080104

So I run the following update and it gives me back 0 rows updated:

update dbo.MetaEvents
set [MetaFYStamp] = cast([Fiscal Year] as varchar(4)),
[MetaPerStamp] = cast([Fiscal Period] as varchar(2)),
[MetaDateStamp] = cast([Date YYYYMMDD] as varchar(8))
from dim_calendar
where dim_calendar.[date] = (select max([invoice date]) from dbo.customer_salesanalysis) and (dim_calendar.division = '')
go

(0 row(s) affected)

Can someone tell me if there is something painfully obvious that I'm missing in my update statement? I've looked at it from every angle and can't figure out why it's not working.

Thanks!!!
Isabelle


Thanks!
Bea Isabelle
Post #439745
Posted Monday, January 7, 2008 12:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 39,982, Visits: 36,350
How many rows are in MetaEvents?

Also, there's no join between MetaEvents (the table you're updating) and calender (the table that the values are coming from)

What determines which row of MetaEvents gets updated with which values from Calender? Or are you updating all rows of MetaEvents with the values from a single row of Calender?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #439752
Posted Monday, January 7, 2008 12:34 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:42 AM
Points: 1,130, Visits: 487
Currently, there are no rows in the MetaEvents table. Yes, I'm trying to update that table with the 1 row that is returned from the select query from the Dim_Calendar table.

Thanks,
Isabelle


Thanks!
Bea Isabelle
Post #439754
Posted Monday, January 7, 2008 12:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 39,982, Visits: 36,350
In that case, you need an insert statement, not an update.

Insert puts new rows into a table, update modifies existing rows in the table.

Insert into dbo.MetaEvents (MetaFYStamp, MetaPerStamp, MetaDateStamp)
select cast([Fiscal Year] as varchar(4)),
cast([Fiscal Period] as varchar(2)),
cast([Date YYYYMMDD] as varchar(8))
from dim_calendar
where dim_calendar.[date] = (select max([invoice date]) from dbo.customer_salesanalysis) and (dim_calendar.division = '')



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #439758
Posted Monday, January 7, 2008 12:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 39,982, Visits: 36,350
As an aside, why are you storing dates as strings instead of datetimes?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #439763
Posted Monday, January 7, 2008 1:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:42 AM
Points: 1,130, Visits: 487
Doh!

You are absolutely correct! I can't even believe I didn't see that! It should have clicked when I responded to you that there are no rows currently in the table.

Thanks so much for you help.

Isabelle


Thanks!
Bea Isabelle
Post #439771
Posted Monday, January 7, 2008 10:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 39,982, Visits: 36,350
Pleasure.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #439877
Posted Tuesday, April 22, 2008 2:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 6, 2009 10:10 AM
Points: 2, Visits: 18
Gail,

I'm new to TSQL and have similar nonfunctional update statement:

set FDIT_SAP_IMPORT.auc=FDIT_SAP_AUC_IMPORT.auc
from FDIT_SAP_IMPORT
where FDIT_SAP_AUC_IMPORT.wbsnumber=FDIT_SAP_IMPORT.wbsnumber;

Any Ideas?
Post #488935
Posted Wednesday, April 23, 2008 12:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 39,982, Visits: 36,350
Maybe.

Can you post the entire update statement please?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #489062
Posted Wednesday, April 23, 2008 5:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 6, 2009 10:10 AM
Points: 2, Visits: 18
USE [FACILITIES]
GO
/****** Object: StoredProcedure [dbo].[FDIT_Transform_SAP_AUC_IMPORT] Script Date: 04/22/2008 15:08:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[FDIT_Transform_SAP_AUC_IMPORT]
AS
UPDATE FDIT_SAP_AUC_IMPORT
SET AUC=AssetNumber+'-'+SNo;

/****** update FDIT_SAP_IMPORT
set FDIT_SAP_IMPORT.auc=FDIT_SAP_AUC_IMPORT.auc
from FDIT_SAP_IMPORT
where FDIT_SAP_AUC_IMPORT.wbsnumber=FDIT_SAP_IMPORT.wbsnumber;****/

update FDIT_SAP_IMPORT
set AUC=FDIT_SAP_AUC_IMPORT.auc
from FDIT_SAP_IMPORT
where FDIT_SAP_AUC_IMPORT.WBSnumber=FDIT_SAP_IMPORT.wbsnumber;

--remove two spaces after wbsnumber
update FDIT_SAP_AUC_IMPORT
set wbsnumber=ltrim(rtrim(wbsnumber));

*********************
I'm also trying this:

declare @theAUC varchar(33)
update fsi
set @theAUC=AssetNumber+'-'+SNo
from fdit_sap_auc_import fsai
inner join fdit_sap_import fsi on fsi.wbsnumber = fsai.wbsnumber
print '@theAUC = ' + cast( @theAUC as varchar(10) )


The above says it affected 3,000 rows but didn't change the table. What I want to do is somehow set @theAUC to the values of the fsai table's AUC column and populate the AUC column in fsi table...?
Thanks for replying! I should also say the target column AUC is populated with NULL values for all records at this point.
Post #489191
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse