SQL Clone
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38917 Visits: 38508
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.

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 (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 214
ok Joe no worries

Could you tel lme what SSMS is


Thanks
Alan
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38917 Visits: 38508
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.

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)
brad.mason5
brad.mason5
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1582 Visits: 2042
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.
Oracle765
Oracle765
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 214
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38917 Visits: 38508
alan_lynch (3/7/2013)
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


I am still at a slight loss here. Based on the limited sample data, that obviously does not reflect your problem domain as it doesn't appear to reflect the error you get, I am trying to figure out why you would want to update values that match. Why update 'Microsoft Office 2003' to 'Microsoft Office 2003' just because it is in the list? Now that you provided a bit more detail, it looks to me like what you really need to do is insert data that does not already exist in the Target Table and ignore the data that does, unless there is more going on than you are showing here.

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 (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 214
Lynn

I just downloaded this sample code from a website and amended accordingly and your right I do not want to update data that already exists so thanks for pointing that out to me.

Finally yes as you say all I want to do is

insert data that does not already exist in the Target Table and ignore the data that does


thanks
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38917 Visits: 38508
Try this:



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

insert into RefTable(software_name_raw,software_name_amended)
select
bt.software_name_raw,
'Needs Updating'
from
BigTable bt
where
not exists(select 1 from RefTable rf where rf.software_name_raw = bt.software_name_raw);

select * from RefTable;




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 (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 214
Hi Lynn


just me again I forgot to mention that yes I want to insert data that does not already exist in the Target Table but if it does exist then I want to update the source table value with col2 value

Target
col1,col2
a,'test1'
b,'test2'
c,'test3'

Source
col1
a,
b,
c,
d

inserted expected results in target
a,'test1' -------------------------------------Matched 'a' so update Source with Targets second column value
b,'test2' -------------------------------------Matched 'b' so update Source with Targets second column value
c,'test3' -------------------------------------Matched 'c' so update Source with Targets second column value
d,'needs updating' -----------------------------Was NO so Match got inserted

expected results in Source
'test1'
'test2'
'test3'
Oracle765
Oracle765
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 214
that was quick i was still mentioning the last thing lol


anyway the code you have just provided is perfect but I still wish to overwrite the first column in the other table with 'Office 2003 professional' etc when they do match
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