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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

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





SO you if there is match in Target, change the Source. If there is no match in Target, add source to target. This right??

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

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


Yes thats right

regards
Alan
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40414 Visits: 38567
this:



if exists(select 1 from sys.tables where object_id = object_id('dbo.BigTable'))
drop table BigTable; --Drop SOURCE
if exists(select 1 from sys.tables where object_id = object_id('dbo.RefTable '))
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;

update bt set
software_name_raw = rt.software_name_amended
from
BigTable bt
inner join RefTable rt
on (bt.software_name_raw = rt.software_name_raw)
where
rt.software_name_amended <> 'Needs Updating';

select * from BigTable;

GO

if exists(select 1 from sys.tables where object_id = object_id('dbo.BigTable'))
drop table BigTable; --Drop SOURCE
if exists(select 1 from sys.tables where object_id = object_id('dbo.RefTable '))
drop table RefTable; --Drop Lookup
GO




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

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

That sort of works but when I run just this part for the second time I get.

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__RefTable__6897329A11007AA7'. Cannot insert duplicate key in object 'dbo.RefTable'.
The statement has been terminated.


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;

update bt set
software_name_raw = rt.software_name_amended
from
BigTable bt
inner join RefTable rt
on (bt.software_name_raw = rt.software_name_raw)
where
rt.software_name_amended <> 'Needs Updating';

select * from BigTable;

GO
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40414 Visits: 38567
alan_lynch (3/10/2013)
Hi Lynn

That sort of works but when I run just this part for the second time I get.

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__RefTable__6897329A11007AA7'. Cannot insert duplicate key in object 'dbo.RefTable'.
The statement has been terminated.


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;

update bt set
software_name_raw = rt.software_name_amended
from
BigTable bt
inner join RefTable rt
on (bt.software_name_raw = rt.software_name_raw)
where
rt.software_name_amended <> 'Needs Updating';

select * from BigTable;

GO



Looks like you are trying to use the same data as input after you have updated it. What would you expect to happen?

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

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

Yeah very close to the expected result but the RefTable has a primary key on it. so if it encounters a duplicate record from the other table it also tries to insert that

the table that I am going to be querying may have duplicate values init

eg
INSERT INTO BigTable
VALUES
('Microsoft Office 2003'),
('Microsoft 2003 Office'),
('Microsoft Office 2003 Professional'),
('Sun Microsystems'),
('Sun Microsystems'),
('Test'),
('Test'),
('Test')
Oracle765
Oracle765
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

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


I have added a group by clause on the end like so which seems to work

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)
group by bt.software_name_raw;



would you agree that this works and we can close the case

thanks
Alan
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