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


Stuck on new fault with my update


Stuck on new fault with my update

Author
Message
Oracle765
Oracle765
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 214
Hi Professionals I am running the following query as advised previously which updates the source table based on a column from the reference table matching...

BEGIN TRANSACTION Inner1;
GO

UPDATE dbsource SET software_name_raw = dbref.software_name_amended
FROM dbo.BigTable dbsource
INNER JOIN (
SELECT software_name_raw,software_name_amended
FROM RefTable
GROUP BY software_name_raw,software_name_amended
) dbref
ON dbref.software_name_raw = dbsource.software_name_raw
go
COMMIT TRANSACTION Inner1;

I have run into a problem which is. If they dont match I need to update the reference tables 2 columns with the new unmatched record to reference something like this

ELSE INSERT INTO RefTable(software_name_raw,software_name_amended)
Values BigTable(software_name_raw,’Needs Updating’)

How can or can this be amended easily.
brad.mason5
brad.mason5
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 2008
I would create temp table or CTE and then use that to do insert update. The "ID" field is your PK of the table.

IF OBJECT_ID('tempdb..#Source','u') IS NOT NULL
DROP TABLE #Source

SELECT dbsource.ID
,dbsource.software_name_raw
,dbref.software_name_amended
,ToInsert = CASE WHEN dbref.software_name_raw IS NULL
THEN 1
ELSE 0
END
INTO #Source
FROM dbo.BigTable dbsource
LEFT JOIN (
SELECT software_name_raw,software_name_amended
FROM RefTable
GROUP BY software_name_raw,software_name_amended
) dbref
ON dbref.software_name_raw = dbsource.software_name_raw

UPDATE BigTable
SET software_name_raw = src.software_name_amended
FROM #Source src
JOIN dbo.BigTable
ON BigTable.ID = src.ID
WHERE src.ToInsert = 0

INSERT INTO dbo.RefTable
(software_name_raw
,software_name_amended
)
SELECT software_name_raw
,'Needs Updating'
FROM #Source
WHERE ToInsert = 1
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24179 Visits: 37948
Do you know what would really help, besides direct access to your system? The DDL for the table(s), some sample data for the tables, the expected results of the query you are working on based on the sample data, and all of this in a readily consumable (meaning cut/paste/run in SSMS) format.

It is really hard to provide good answers based on just some code that apparently doesn't really work.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Oracle765
Oracle765
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 214
Hi Brad.

The software_name_raw on the RefTable is the Primary Key

Does that help
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24179 Visits: 37948
alan_lynch (3/6/2013)
Hi Brad.

The software_name_raw on the RefTable is the Primary Key

Does that help


For better answers to your questions, read this: http://www.sqlservercentral.com/articles/Best+Practices/61537/.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Oracle765
Oracle765
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 214
Sorry Lynn

The BigTable.software_name_raw contains

SOFTWARE_NAME_RAW
Microsoft Office 2003 Professional Edition, Office 2003 Professional
Microsoft Office 2003 Professional Enterprise Edition, Office 2003 Professional
Microsoft 2003 Office Professional, Office 2003 Professional

and the RefTable contains a further column

SOFTWARE_NAME_RAW,SOFTWARE_NAME_AMENDED

Microsoft Office 2003 Professional Edition, Office 2003 Professional
Microsoft Office 2003 Professional Enterprise Edition, Office 2003 Professional
Microsoft 2003 Office Professional, Office 2003 Professional

So if the two columns match then update the BigTable with the software_name_amended column from the RefTable

If there is no initial match then I want to insert into the RefTable a new found reference

EG Microsoft 2003 PRO, 'Needs Updating'

Hope this helps
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24179 Visits: 37948
alan_lynch (3/6/2013)
Sorry Lynn

The BigTable.software_name_raw contains

SOFTWARE_NAME_RAW
Microsoft Office 2003 Professional Edition, Office 2003 Professional
Microsoft Office 2003 Professional Enterprise Edition, Office 2003 Professional
Microsoft 2003 Office Professional, Office 2003 Professional

and the RefTable contains a further column

SOFTWARE_NAME_RAW,SOFTWARE_NAME_AMENDED

Microsoft Office 2003 Professional Edition, Office 2003 Professional
Microsoft Office 2003 Professional Enterprise Edition, Office 2003 Professional
Microsoft 2003 Office Professional, Office 2003 Professional

So if the two columns match then update the BigTable with the software_name_amended column from the RefTable

If there is no initial match then I want to insert into the RefTable a new found reference

EG Microsoft 2003 PRO, 'Needs Updating'

Hope this helps



Nope. I don't see any DDL for the table(s), nor insert statements with sample (not real) data, nor even what the expected results are based on the sample data.

Guess I'll move along and see if there are others who I may be able help.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Oracle765
Oracle765
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 214
Ok Lynn if thats what you want to do and move on and help someone else then thats fine.

I am new to SqlServer so I dont know how to do the insert statement properly, thats why I asked this in my initial enquiry asking if data can be inserted into the reference table if it does not exist during my update.

I will await Brad's reply he seems to know what I mean
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24179 Visits: 37948
alan_lynch (3/6/2013)
Ok Lynn if thats what you want to do and move on and help someone else then thats fine.

I am new to SqlServer so I dont know how to do the insert statement properly, thats why I asked this in my initial enquiry asking if data can be inserted into the reference table if it does not exist during my update.

I will await Brad's reply he seems to know what I mean


Take the time to read the article I gave you the link to, it will help with everything I asked you to provide.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
joeroshan
joeroshan
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 1987
Alan,

What you need to do is right click the Both tables in SSMS, select Script table as Create , paste the code in the forum. Otherwise whatever suggestions we give will be based on assumptions, that can give you wrong results.

Lynn is willing to help you and he is one of very active members here.

For your question, If software_name_raw is pk for your RefTable, I donot see a point in using group by in your update statement.

Edit (Reffered Lynn with wrong pronoun. Because we only see their valuable posts , rarely know them in person. Corrected with apologies)

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe


Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
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