Stuck on new fault with my update

  • 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.

  • 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

  • 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.

  • Hi Brad.

    The software_name_raw on the RefTable is the Primary Key

    Does that help

  • 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/.

  • 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

  • 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.

  • 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

  • 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.

  • 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[/url]

  • The one thing I would suggest reading about is the MERGE statement. This may be what you really need.

    Start here: http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx.

  • ok Joe no worries

    Could you tel lme what SSMS is

    Thanks

    Alan

  • alan_lynch (3/6/2013)


    ok Joe no worries

    Could you tel lme what SSMS is

    Thanks

    Alan

    SQL Server Management Studio. Just happens to be the main tool we all use when working with MS SQL Server.

  • Hey Alan,

    I am basically guessing here what data looks like. If you could provide more detail you would be able to get additional expert advice.

    If your PK ID is software_name_raw then replace "ID" with "software_name_raw"

    Also in the sub query (LEFT JOIN (sub query)) uses group by for name and amended name. If the values are unique in the table for name and amended name then this is not required and you can do a straight LEFT JOIN to the table.

    The concept is I propose is to use temp table to gather all data and mark which rows should be inserted or updated (ToInsert). Then use this data to do Insert and Update.

    If this were the same table and you are on SQL Server 2008 or greater you could use MERGE which I have been using a lot for my projects.

  • right Lynn I will try again.

    I have had a look at your merge statement which has been useful the only thing is i have one error from the query below.

    Please bare in mind it works fine as it is, but when I add a duplicate row to the Source(BigTable) for my test data-- like so ('Microsoft Office 2003 Professional'),

    I get the error message

    Msg 8672, Level 16, State 1, Line 9

    The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

    in my production data I will have many many rows the same from the source table but they are to be unique in the lookuptable

    drop table BigTable --Drop SOURCE

    drop table RefTable --Drop Lookup

    --Create a Source table

    CREATE TABLE BigTable

    (

    software_name_raw VARCHAR(255)

    )

    GO

    --Insert records into Source table

    INSERT INTO BigTable

    VALUES

    ('Microsoft Office 2003'),

    ('Microsoft 2003 Office'),

    ('Microsoft Office 2003 Professional'),

    ('Sun Microsystems')

    GO

    --Create LOOKUP table

    CREATE TABLE RefTable

    (

    software_name_raw VARCHAR(255) PRIMARY KEY,

    software_name_amended Varchar (255)

    )

    GO

    --Insert records into Lookup table

    INSERT INTO RefTable

    VALUES

    ('Microsoft Office 2003', 'Office 2003'),

    ('Microsoft 2003 Office', 'Office 2003'),

    ('Microsoft Office 2003 Professional', 'Office 2003'),

    ('Adobe', 'Adobe Inc')

    GO

    SELECT * FROM BigTable as Source_TABLE

    SELECT * FROM RefTable as Lookup_TABLE

    GO

    --Next I will use the MERGE SQL command to synchronize the target table

    --with the refreshed data coming from the LOOKUP table.

    --MERGE SQL statement – Part 2

    --Synchronize the lookup table with

    --refreshed data from source table

    MERGE RefTable AS TARGET

    USING BigTable AS SOURCE

    ON (TARGET.software_name_raw = SOURCE.software_name_raw)

    --When records are matched, update the records if there is any change

    WHEN MATCHED AND TARGET.software_name_raw = SOURCE.software_name_raw THEN

    UPDATE SET TARGET.software_name_raw = SOURCE.software_name_raw

    --When no records are matched, insert the incoming records from source into the lookup table

    WHEN NOT MATCHED BY TARGET THEN

    INSERT (software_name_raw, software_name_amended)

    VALUES (SOURCE.software_name_raw, 'Needs Updating')

    --$action specifies a column of type nvarchar(10)

    --in the OUTPUT clause that returns one of three

    --values for each row: ‘INSERT’, ‘UPDATE’, or ‘DELETE’,

    --according to the action that was performed on that row

    OUTPUT $action,

    DELETED.software_name_raw AS Lookupsoftware_name_raw,

    DELETED.software_name_amended AS Lookupoftware_name_amended,

    INSERTED.software_name_raw AS Sourcesoftware_name_raw,

    INSERTED.software_name_amended AS Sourcesoftware_name_amended;

    GO

    select * from BigTable as source_table

    select * from RefTable as lookuptable

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply