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 123»»»

Stuck on new fault with my update Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 6:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
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.

Post #1427718
Posted Wednesday, March 6, 2013 7:32 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 958, Visits: 920
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
Post #1427722
Posted Wednesday, March 6, 2013 7:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 22,995, Visits: 31,478
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.



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)
Post #1427725
Posted Wednesday, March 6, 2013 8:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
Hi Brad.

The software_name_raw on the RefTable is the Primary Key

Does that help
Post #1427731
Posted Wednesday, March 6, 2013 8:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 22,995, Visits: 31,478
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/.



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)
Post #1427733
Posted Wednesday, March 6, 2013 8:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
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

Post #1427736
Posted Wednesday, March 6, 2013 9:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 22,995, Visits: 31,478
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.



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)
Post #1427738
Posted Wednesday, March 6, 2013 9:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
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
Post #1427740
Posted Wednesday, March 6, 2013 9:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 22,995, Visits: 31,478
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.



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)
Post #1427741
Posted Wednesday, March 6, 2013 9:18 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:10 AM
Points: 615, Visits: 1,259
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
Custom cleanup script for backups
Post #1427744
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse