June 29, 2011 at 1:04 pm
I am trying to update a table of addresses using fields from another table of addresses for the address type mailing. If it is a PO box, I want to update the 'Mailing' addresstype and if it is not a PO box, I want to update the 'Main' address type.
There are 56 records that need to be updated in the target table which are identified by this query.
select *
from ADDRESSES TGT
join AddressUpdates SRC
on TGT.id=SRC.id
where SRC.address like 'PO%';
But running this query returns only 9 records.
select *
from ADDRESSES TGT
join AddressUpdates SRC
on TGT.id=SRC.id
where SRC.address like 'PO%' AND TGT.addresstype='Mailing';
This is the update statement I am running, but as expected, only 9 results are updated. How can iI get it to update all 56 records for the address type 'Mailing'? I think part of my problem is that some address ID's dont already have a mailing address, so I would need to insert a new row and set the type to mailing. I also tryed using the merge statement but was confused about how to use a where clause with it (it seemed like I should put the where clause arguments in the when clause, but I wasn't sure)
update TGT
set TGT.address=SRC.address
from ADDRESSES TGT
join AddressUpdates SRC
on TGT.id=SRC.id
where SRC.address like 'PO%' AND TGT.addresstype='Mailing';
I plan to use a "not like 'PO%'" to update all the 'Main' addresses.
Any help would be appreciated!
-Danny
June 29, 2011 at 1:09 pm
Your query is restricting the result set to just records that are PO box and mailing address types.
Try this:
select *
from ADDRESSES TGT
join AddressUpdates SRC
on TGT.id=SRC.id
where SRC.address like 'PO%'
AND TGT.addresstype=
Case when SRC.address like 'PO%' then 'Mailing'
Else 'Main'
End;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 29, 2011 at 1:16 pm
This query works, but still returns 9 results. =(
June 29, 2011 at 1:23 pm
Here's another
select *
from ADDRESSES TGT
join AddressUpdates SRC
on TGT.id=SRC.id
where (SRC.address like 'PO%'
AND TGT.addresstype= 'Mailing'
)
Or (SRC.address not like 'PO%'
AND TGT.addresstype= 'Main'
)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 29, 2011 at 1:23 pm
Shouldn't this work for you , It seems pretty straight forward
update TGT
set TGT.address=SRC.address
from ADDRESSES TGT
join AddressUpdates SRC
on TGT.id=SRC.id
where SRC.address like 'PO%'
update TGT
set TGT.address=SRC.address
from ADDRESSES TGT
join AddressUpdates SRC
on TGT.id=SRC.id
where SRC.address like 'PO%' AND ISNULL(TGT.addresstype,'Mailing')='Mailing';
June 29, 2011 at 1:27 pm
Jayanth_Kurup (6/29/2011)
Shouldn't this work for you , It seems pretty straight forwardupdate TGT
set TGT.address=SRC.address
from ADDRESSES TGT
join AddressUpdates SRC
on TGT.id=SRC.id
where SRC.address like 'PO%'
No. Only if the address is a POBox when the address type is 'mailing' would that work. He needs to be able to update 'main' address types when the address is not a POBOX.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 29, 2011 at 1:36 pm
Thanks SQLRNNR,
That query also executes but returns 849 rows. I know there are only 56 rows in the SRC table with ID's that match the TGT table and start with 'PO'. Also, we havent come up with something to handle the insertion of an address if no rows exists with an addresstpe of 'Mailing' in the source table.
The logic needs to be something like this.
Where SRC.id=TGT.id
and SRC.address like 'PO%'
update TGT.address where TGT.addresstype='Mailing'
else insert into tgt (id, address, addresstype) values (src.id, src.address, 'Main')
with a total of 56 rows being inserted or updated.
Thank you very much for your help, this one is a little too tough for my current skill level.
-Danny
June 29, 2011 at 1:46 pm
What you just said does not match what your first post said.
Please provide table structures and sample data in order to get an accurate query.
In the meantime, you should look at the Merge Statement.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 29, 2011 at 2:24 pm
You are right, my first sentence was misleading. Later in my first post I explained "I think part of my problem is that some address ID's dont already have a mailing address, so I would need to insert a new row and set the type to mailing. I also tryed using the merge statement but was confused about how to use a where clause with it (it seemed like I should put the where clause arguments in the when clause, but I wasn't sure)"
Here are the simplified schema's. Sorry for leaving some of this information out initially, I really appreciate your help and did not leave it out on purpose. I also realized that I was using the term address id, which is really an individuals account id, so this also probably mislead you.
SRC
accountid, address
TGT
accountid(pk), addresstype(pk), address
There are 7 addresstypes possible. In the SRC table, an account can have 0-7 records, 0-1 record for each address type.
Here is my attempt at a merge statement that would do what I need it to do (except my syntax is incorrect):
MERGE source.addresses as SRC
USING target.addresses as TGT
ON TGT.accountid=SRC.accountid
WHEN MATCHED AND SRC.address like 'PO%' AND TGT.addresstype='Mailing'
UPDATE SET TGT.address=SRC.address
WHEN MATCHED AND SRC.address not like 'PO%' AND TGT.addresstype='Main'
UPDATE SET TGT.address=SRC.address
WHEN NOT MATCHED AND SRC.address like 'PO%'
INSERT (TGT.accountid, TGT.addresstype, TGT.address)
VALUES (SRC.accountid, 'Mailing', SRC.address)
WHEN NOT MATCHED AND SRC.address not like 'PO%'
INSERT (TGT.accountid, TGT.addresstype, TGT.address)
VALUES (SRC.accountid, 'Main', SRC.address);
Thanks again.
-Danny
June 29, 2011 at 5:09 pm
Hey SQLRNNR! I figured it out using the MERGE statement. I had to create two seperate statements, but thats okay.
When running these large queries, is there any way to spit out which rows were modified/inserted so that I can check that the merge worked properly? I am updating a few thousand rows in each query.
Thanks again for your help!
-Danny
June 30, 2011 at 3:17 am
This can be done in one MERGE statement, and yes there is a way to return the modifications made:
DECLARE @Changes TABLE
(
account_idINTEGER PRIMARY KEY,
the_addressNVARCHAR(100) NOT NULL
);
DECLARE @data TABLE
(
account_idINTEGER NOT NULL,
address_typeVARCHAR(20) NOT NULL,
the_addressNVARCHAR(100) NOT NULL,
PRIMARY KEY (account_id, address_type)
);
MERGE @data AS d
USING @Changes AS c ON
c.account_id = d.account_id
WHEN MATCHED AND
(d.address_type = 'Mailing' AND c.the_address LIKE 'PO%')
OR
(d.address_type = 'Main' AND c.the_address NOT LIKE 'PO%')
THEN
UPDATE SET the_address = c.the_address
WHEN NOT MATCHED
THEN
INSERT
(account_id, address_type, the_address)
VALUES
(
c.account_id,
CASE
WHEN c.the_address LIKE 'PO%' THEN 'Mailing'
ELSE 'Main'
END,
c.the_address
)
OUTPUT
$action,
INSERTED.*,
DELETED.*;
For best performance, ensure there is a unique or primary key constraint on the account_id column in the changes table.
For full MERGE syntax and examples, see http://technet.microsoft.com/en-us/library/bb510625.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2011 at 12:24 pm
June 30, 2011 at 4:13 pm
Yes Jayanth, I finally caught that I had it flipped. And thank you kiwi for that merge statement. It is an improvement on my two merge statements and also outputs the results. I really appreciate you help!
June 30, 2011 at 11:25 pm
No worries. Thanks for posting an interesting question.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 5, 2011 at 12:02 am
Oh no, I was running this code in sql server 2008 but now have to run it in sql server 2005 which apparently doesn't have the merge command.
I know this would now be a good question for a separate forum, but since you already understand what i am doing, any suggestions on a non-merge technique?
Thank you very much!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply