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 Thursday, March 07, 2013 9:26 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 22,492, Visits: 30,199
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??



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 #1428367
Posted Thursday, March 07, 2013 10:19 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, March 03, 2014 4:58 PM
Points: 130, Visits: 214
Hi Lynn


Yes thats right

regards
Alan
Post #1428385
Posted Friday, March 08, 2013 7:25 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 22,492, Visits: 30,199
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





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 #1428573
Posted Sunday, March 10, 2013 6:19 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, March 03, 2014 4:58 PM
Points: 130, 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
Post #1429042
Posted Sunday, March 10, 2013 9:58 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 22,492, Visits: 30,199
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?



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 #1429059
Posted Sunday, March 10, 2013 10:10 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, March 03, 2014 4:58 PM
Points: 130, 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')
Post #1429060
Posted Sunday, March 10, 2013 10:37 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, March 03, 2014 4:58 PM
Points: 130, 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
Post #1429065
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse