September 13, 2013 at 2:36 pm
Hi all -
I am preparing create tables with test data, but I thought this might be so simple and generic that it might not require them. I'm probably missing somthing simple here.
After a table load I'm trying to set one column's data using the first char of another column. This is what I tried, but it seems to be updating the entire column to the same value -
UPDATE DIM.DocControlProfile
SET DocType = SUBSTRING(STG.CurrDocNumber,1,1)
FROM STG.Staging AS STG
INNER JOIN DIM.DocControlProfile AS DC
ON STG.DocContProfileID = DC.DocControlID
Any generic syntax advice is appreciated but if needed I will finish the create table and add data script.
Thanks in advance.
September 13, 2013 at 3:16 pm
We will need sample data in order to tell you exactly what the problem is... It looks like your join is not doing what you expect it is.
Said another way, your syntax is correctly updating the docControlProfile's doctype to the first character of the last (last is not defined) currentDocNumber in staging with the DocContProfileID of the DocControlProfile.
Your problem is most likely due to having multiple records in the staging table for each profile. You should be able to see identify the problem by running
select DC.DocControlID, STG.DocContProfileID, DIM.DocType, STG.CurrDocNumber,
FROM STG.Staging AS STG
INNER JOIN DIM.DocControlProfile AS DC
ON STG.DocContProfileID = DC.DocControlID
order by DC.DocControlID, STG.DocContProfileID
I hope that helps,
-Alex.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
September 13, 2013 at 3:37 pm
I always try to put the table I am updating as first in the FROM clause and use the alias as the UPDATE target.
UPDATE DC
SET DocType = SUBSTRING(STG.CurrDocNumber,1,1)
FROM DIM.DocControlProfile AS DC
INNER JOIN STG.Staging AS STG
ON STG.DocContProfileID = DC.DocControlID
Not sure this will help you. As the previous poster said this may be an issue with the join or it may be that the statement is being interpreted as
UPDATE DIM.DocControlProfile
SET DocType = (
SELECT SUBSTRING(STG.CurrDocNumber,1,1)
FROM DIM.DocControlProfile AS DC
INNER JOIN STG.Staging AS STG
ON STG.DocContProfileID = DC.DocControlID
)
where the DocType is updated with the first value of the SELECT
Wish I could test this for you as I could be totally wrong:unsure:, unfortunately I haven't put SQL Server back on my laptop since a rebuild.
September 13, 2013 at 4:27 pm
Try this.
UPDATE dim.doccontrolprofile
SET DocType = SUBSTRING(STG.CurrDocNumber, 1, 1)
FROM dim.doccontrolprofile dc
INNER JOIN staging stg ON STG.DocContProfileID = DC.DocControlID
September 13, 2013 at 7:42 pm
I am out for the weekend. I want to thank the people that have already replied. I really like this forum. I will look more at suggestions on monday and provide more details.
thank you again for looking at this!!!!!!!!
September 16, 2013 at 12:01 am
Why not simply
UPDATE DIM.DocControlProfile
SET DocType = (select( SUBSTRING(CurrDocNumber,1,1)
FROM STG.Staging
where DocContProfileID = DIM.DocControlProfile.DocControlID)
September 19, 2013 at 1:06 pm
hello all again - I didn't want to bail out without showing my appreciation for the help on this forum. I got a new "top priorty" when I got in this week. I am back to it now and I think I'm barking up the wrong tree. I created this test harness and everything is working exactly as expected so I need to look into the data more. This is of no real use to anyone but I thought I'd throw it out there out of respect for the forum. Like I said, this all works as expected, but not on the data I have.
Thanks to all who chimed in on this - I very much appreciate
USE tempdb
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZZZStageTest]') AND type in (N'U'))
DROP TABLE [dbo].ZZZStageTest
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZZZDocContProfile]') AND type in (N'U'))
DROP TABLE [dbo].ZZZDocContProfile
GO
CREATE TABLE [ZZZDocContProfile](
[DocContID] [int] IDENTITY(1,1) NOT NULL,
[AgencyNumber] [varchar](3) NULL,
[DocumentType] [varchar](1) NULL,
[DocContTitle] [varchar](40) NULL,
)
GO
CREATE TABLE [ZZZStageTest](
[StageTestID] [bigint] IDENTITY(1,1) NOT NULL,
[DocContProfileID] [int] NULL,
[CurDocNum] VARCHAR(20),
[CurrentDocType] [char](1) NULL,
[DocContTitle] VARCHAR(50) NULL
)
GO
--Load some test data
INSERT INTO [ZZZDocContProfile] (
[AgencyNumber]
,[DocumentType]
,[DocContTitle])
VALUES
('123', 'A','TEST1')
,('123', '', 'TEST2')
,('300', '', 'TEST4')
,('300', '', 'TEST4')
,('500', 'C', 'TEST5')
GO
INSERT INTO [ZZZStageTest]
([DocContProfileID]
,[CurDocNum]
,[CurrentDocType]
,[DocContTitle])
VALUES
('1', '12345', '', 'STG1')
,('2', 'ABCD', '', 'STG2')
,('3', 'THIS', '', 'STG3')
,('4', 'OTHER', '','STG4')
,('5', 'C', '', 'STG5')
GO
UPDATE DC
SET DC.[DocumentType] = SUBSTRING(STG.[CurDocNum],1,1)
FROM [ZZZDocContProfile] AS DC
INNER JOIN [ZZZStageTest] AS STG
ON STG.[DocContProfileID] = DC.[DocContID]
GO
SELECT *
FROM [ZZZDocContProfile] AS DC
INNER JOIN [ZZZStageTest] AS STG
ON STG.[DocContProfileID] = DC.[DocContID]
GO
September 19, 2013 at 3:50 pm
With the tabale you posted there is a cardinality problem.
The syntax UPDATE FROM is scorned by some people, because it has a possible ambiguity. I don't share that opinion, because I think that rightly used UPDATE FROM is very useful. But there is no one to protect you when you slip.
With the sample data you posted DocContProfileID in the staging table is unique, but there is no constraint to enforce this. If there are multiple values, it is not deterministic which row the target table will be updated from.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 19, 2013 at 8:50 pm
Erland Sommarskog (9/19/2013)
The syntax UPDATE FROM is scorned by some people, because it has a possible ambiguity. I don't share that opinion, because I think that rightly used UPDATE FROM is very useful. But there is no one to protect you when you slip.
+1000
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2013 at 10:21 am
Jeff Moden (9/19/2013)
Erland Sommarskog (9/19/2013)
The syntax UPDATE FROM is scorned by some people, because it has a possible ambiguity. I don't share that opinion, because I think that rightly used UPDATE FROM is very useful. But there is no one to protect you when you slip.+1000
Can I ask for clarification on this? Using the most recent update statement referenced in this post:
UPDATE DC
SET DC.[DocumentType] = SUBSTRING(STG.[CurDocNum],1,1)
FROM [ZZZDocContProfile] AS DC
INNER JOIN [ZZZStageTest] AS STG
ON STG.[DocContProfileID] = DC.[DocContID]
What would be the preferred way to write this?
September 20, 2013 at 3:50 pm
The almost ANSI-compatible way of writing it is:
UPDATE ZZZDocContProfile
SET DC.[DocumentType] = (SELECT SUBSTRING(STG.[CurDocNum],1,1)
FROM [ZZZStageTest] AS STG
WHERE STG.[DocContProfileID] = DC.[DocContID])
FROM [ZZZDocContProfile] AS DC
WHERE EXISTS (SELECT *
FROM [ZZZStageTest] AS STG
WHERE STG.[DocContProfileID] = DC.[DocContID])
I almost ANSI-compatible, because there is still a FROM clause. In a pure ANSI database, you would not be able to define an alias for the target table.
The advantage here is that if you screw up on cardinality, you will get a run-time error.
The disadvantage is that the syntax requires you to have the condition in two places, and you can easily for get the EXISTS clause, which causes you to update all rows in the target table. The row that are not matched in the source will be set to NULL.
And of course, if you want to update multiple columns, you are lost, since T-SQL does not have row constructors:
Some people prefer to do this with MERGE:
MERGE ZZZDocContProfile AS DC
USING [ZZZStageTest] AS STG ON STG.[DocContProfileID] = DC.[DocContID]
WHEN MATCHED THEN
UPDATE
SET DocumentType = SUBSTRING(STG.[CurDocNum],1,1)
;
Here you can update as many columns as you like. If you have a cardinality error, you will get a run-time error if there is ambiguity.
But this whole discussion is not about which syntax you should use. It is about that you need to know what you are doing. The rows you update in the target table should match zero or one rows in the source, else there is an ambiguity problem. Depending on the syntax you use, you will get a run-time error or an undefined result, but no matter what, your idea of how to update was wrong.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 20, 2013 at 4:16 pm
Thank you for the explanations and examples!! This "easy" problem has caused much frustration.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply