Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update a table from select statement results


Update a table from select statement results

Author
Message
Isabelle2378
Isabelle2378
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 516
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59267 Visits: 44732
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

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


Isabelle2378
Isabelle2378
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 516
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59267 Visits: 44732
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

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59267 Visits: 44732
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

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


Isabelle2378
Isabelle2378
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 516
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59267 Visits: 44732
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

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


beeniecopter
beeniecopter
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59267 Visits: 44732
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

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


beeniecopter
beeniecopter
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search