January 7, 2008 at 12:16 pm
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? :crazy: I've looked at it from every angle and can't figure out why it's not working.
Thanks!!!
Isabelle
Thanks!
Bea Isabelle
January 7, 2008 at 12:30 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2008 at 12:34 pm
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
January 7, 2008 at 12:39 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2008 at 12:50 pm
As an aside, why are you storing dates as strings instead of datetimes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2008 at 1:15 pm
Doh! :blush:
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
January 7, 2008 at 10:57 pm
Pleasure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2008 at 2:44 pm
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?
April 23, 2008 at 12:10 am
Maybe.
Can you post the entire update statement please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2008 at 5:31 am
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.
April 23, 2008 at 5:44 am
You're referencing the table FDIT_SAP_AUC_IMPORT in the set and in the where, but it's not in the from.
Is this perhaps what you want?
update FDIT_SAP_IMPORT
set AUC=FDIT_SAP_AUC_IMPORT.auc
from FDIT_SAP_AUC_IMPORT
where FDIT_SAP_AUC_IMPORT.WBSnumber=FDIT_SAP_IMPORT.wbsnumber;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply