Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Complex Update Problem

Author  Topic 

bogus
Starting Member

41 Posts

Posted - 2014-11-17 : 21:48:18
I am pulling my hair out over this one... can't see it for the forest, I suppose.

I have done quite a bit of internet searching and can't find a solid answer. An example I did find was for SQL Server 2008, and my query matched, yet I learn there are differences with SQL 2012 that might just explain why this update isn't...


UPDATE cm
SET cm.PreviousTransaction_DT = t.Transaction_DT,
cm.PreviousTransactionType = t.TransactionType,
cm.PreviousTransactionChannel = t.Channel
FROM CustomerMaster cm
INNER JOIN (SELECT TOP 1 CustomerID, Transaction_DT,
TransactionType, Channel
FROM [dbo].[vw_Purchase_Request_Transactions]
ORDER BY Transaction_DT DESC) t
ON t.CustomerID = cm.CustomerID AND
(t.TransactionType BETWEEN 2 AND 4) AND
t.Channel < 4 AND
t.Transaction_DT <= cm.LastTransaction_DT


All data is present to make this work... yet it updates zero records.

I even converted it to a simple select with the JOIN(SELECT) construct, and it still finds nothing - with a simplified "ON" condition and a WHERE, looking for a specific Customer ID:


SELECT t.customerid, cm.CustomerID, cm.LastTransaction_DT, cm.PreviousTransaction_DT, cm.PreviousTransactionChannel, cm.PreviousTransactionType
FROM CustomerMaster cm
INNER JOIN (SELECT TOP 1 CustomerID, Transaction_DT, TransactionType, Channel
FROM [dbo].[vw_Purchase_Request_Transactions]
ORDER BY Transaction_DT) t ON t.CustomerID = cm.CustomerID
WHERE cm.CustomerID = '4605950';


And when I convert the select query into a more conventional structure, like this:


SELET t.customerid, cm.CustomerID, cm.LastTransaction_DT, cm.PreviousTransaction_DT, cm.PreviousTransactionChannel, cm.PreviousTransactionType
FROM CustomerMaster cm
INNER JOIN [dbo].[vw_Purchase_Request_Transactions] t
ON t.CustomerID = cm.CustomerID
WHERE cm.CustomerID = '4605950';


The view is a union of two transaction tables and it's working fine. Even rapidly.

But this overall construct isn't working!

Any help would be greatly appreciated. Heading home to hit the books.... THANKS!!!

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-11-18 : 08:55:51
Silly question is it defo an INNER JOIN?


With the full inner join you are also looking for a match on the joining table, Left Join will bring you everything back on the main table even if no match on the joining table(right).

SELET t.customerid, cm.CustomerID, cm.LastTransaction_DT, cm.PreviousTransaction_DT, cm.PreviousTransactionChannel, cm.PreviousTransactionType
FROM CustomerMaster cm
LEFT JOIN [dbo].[vw_Purchase_Request_Transactions] t
ON t.CustomerID = cm.CustomerID
WHERE cm.CustomerID = '4605950';






We are the creators of our own reality!
Go to Top of Page

bogus
Starting Member

41 Posts

Posted - 2014-11-18 : 12:12:10
quote:
Originally posted by sz1

Silly question is it defo an INNER JOIN?


With the full inner join you are also looking for a match on the joining table, Left Join will bring you everything back on the main table even if no match on the joining table(right).

SELET t.customerid, cm.CustomerID, cm.LastTransaction_DT, cm.PreviousTransaction_DT, cm.PreviousTransactionChannel, cm.PreviousTransactionType
FROM CustomerMaster cm
LEFT JOIN [dbo].[vw_Purchase_Request_Transactions] t
ON t.CustomerID = cm.CustomerID
WHERE cm.CustomerID = '4605950';






We are the creators of our own reality!



Good question... and yes, inner is the optimal. In these tests, it would need to be, but there is data to satisfy the query.

Thanks for the feedback, I will test the left join. I am not one to discard an idea without trying it first!
Go to Top of Page

bogus
Starting Member

41 Posts

Posted - 2014-11-18 : 12:48:10
I am seeing the problem... by using my base query,I changed from a inner join to a left join - the join is not returning anything on the "(select...) t"!!! The left join revealed that there wasn't anything in "t" - BUT THERE IS!!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 13:37:33
Show us some sample data that shows your issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bogus
Starting Member

41 Posts

Posted - 2014-11-18 : 14:06:18
quote:
Originally posted by tkizer

Show us some sample data that shows your issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/




Sure!

Raw data from the transaction view:

Customer ID Transaction Transaction Channel
Date Type
4605950 2008-01-01 2 2
4605950 2009-12-12 2 1
4605950 2009-12-26 2 1


Raw data from CustomerMaster:

Customer ID Last Transaction Previous Previous Previous
Date Date Channel Type
4605950 2009-12-26 2009-12-12 1 2


When I run the query - which I am testing, so I am expecting the results listed for CustomerMaster - I get NOTHING.

Just null data.

Now, I remove the "TOP 1" and I get data, I just can't pick one record... Confusing, eh?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 14:09:29
Well what does the TOP 1 query return? Please run this and show us the output: SELECT TOP 1 CustomerID, Transaction_DT, TransactionType, Channel
FROM [dbo].[vw_Purchase_Request_Transactions]
ORDER BY Transaction_DT

I'm thinking you need to add a WHERE clause to it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bogus
Starting Member

41 Posts

Posted - 2014-11-18 : 15:34:18
quote:
Originally posted by tkizer

Well what does the TOP 1 query return? Please run this and show us the output: SELECT TOP 1 CustomerID, Transaction_DT, TransactionType, Channel
FROM [dbo].[vw_Purchase_Request_Transactions]
ORDER BY Transaction_DT

I'm thinking you need to add a WHERE clause to it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/




Tara,

As usual... I love your insights.

Per your request, here is the output:


Customer ID Transaction Transaction Channel
Date Type
1021756 1900-01-01 2 2


This record has some serious issues... but it still presents as expected. That transaction date is a total error, however, it is still a value and needs to be fixed (looks like a result of the initial load of the database).
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 15:41:30
Move the WHERE clause to the SELECT TOP query:

SELECT t.customerid, cm.CustomerID, cm.LastTransaction_DT, cm.PreviousTransaction_DT, cm.PreviousTransactionChannel, cm.PreviousTransactionType
FROM CustomerMaster cm
INNER JOIN (SELECT TOP 1 CustomerID, Transaction_DT, TransactionType, Channel
FROM [dbo].[vw_Purchase_Request_Transactions] WHERE CustomerID = '4605950'
ORDER BY Transaction_DT) t ON t.CustomerID = cm.CustomerID


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bogus
Starting Member

41 Posts

Posted - 2014-11-18 : 16:41:47
quote:
Originally posted by tkizer

Move the WHERE clause to the SELECT TOP query:

SELECT t.customerid, cm.CustomerID, cm.LastTransaction_DT, cm.PreviousTransaction_DT, cm.PreviousTransactionChannel, cm.PreviousTransactionType
FROM CustomerMaster cm
INNER JOIN (SELECT TOP 1 CustomerID, Transaction_DT, TransactionType, Channel
FROM [dbo].[vw_Purchase_Request_Transactions] WHERE CustomerID = '4605950'
ORDER BY Transaction_DT) t ON t.CustomerID = cm.CustomerID


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/




Ok... that does work... now for the million dollar question... how do I trick this into actually working with the update?

To test the theory, I changed the Where to:

WHERE CustomerID = cm.CustomerID

-- or --

WHERE CustomerID = CustomerMaster.CustomerID

And in both cases, I get squiggly red lines and the mean little message from SQL that states:

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "CustomerMaster.customerid" could not be bound.

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "cm.customerid" could not be bound.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 17:18:35
Show me the modified update.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bogus
Starting Member

41 Posts

Posted - 2014-11-18 : 18:13:11
quote:
Originally posted by tkizer

Show me the modified update.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



This is the original update. I am assuming that if the select fails, so would the update.


UPDATE cm
SET cm.PreviousTransaction_DT = t.Transaction_DT,
cm.PreviousTransactionType = t.TransactionType,
cm.PreviousTransactionChannel = t.Channel
FROM CustomerMaster cm
INNER JOIN (SELECT TOP 1 CustomerID, Transaction_DT,
TransactionType, Channel
FROM [dbo].[vw_Purchase_Request_Transactions]
WHERE CustomerID = CustomerMaster.CustomerID
ORDER BY Transaction_DT DESC) t
ON t.CustomerID = cm.CustomerID AND
(t.TransactionType BETWEEN 2 AND 4) AND
t.Channel < 4 AND
t.Transaction_DT <= cm.LastTransaction_DT


I make the same change to the update and if gets the same error:

Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "CustomerMaster.CustomerID" could not be bound.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 18:19:42
You can't reference CustomerMaster cm in the SELECT TOP 1 query as it doesn't exist in there. I'm trying to wrap my head around what you are intending to do to see if I can fix it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 18:25:39
Try this in a test environment:

UPDATE cm
SET cm.PreviousTransaction_DT = t.Transaction_DT,
cm.PreviousTransactionType = t.TransactionType,
cm.PreviousTransactionChannel = t.Channel
FROM CustomerMaster cm
INNER JOIN
(
SELECT CustomerID, Transaction_DT, TransactionType, Channel, ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY Transaction_DT DESC AS RowNo
FROM [dbo].[vw_Purchase_Request_Transactions]
) t
ON t.CustomerID = cm.CustomerID AND t.Transaction_DT <= cm.LastTransaction_DT
WHERE (t.TransactionType BETWEEN 2 AND 4) AND
t.Channel < 4 AND
t.RowNo = 1

Switch it to a SELECT first to verify:

SELECT t.Transaction_DT, t.TransactionType, t.Channel
--UPDATE cm
--SET cm.PreviousTransaction_DT = t.Transaction_DT,
-- cm.PreviousTransactionType = t.TransactionType,
-- cm.PreviousTransactionChannel = t.Channel
FROM CustomerMaster cm
INNER JOIN
(
SELECT CustomerID, Transaction_DT, TransactionType, Channel, ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY Transaction_DT DESC AS RowNo
FROM [dbo].[vw_Purchase_Request_Transactions]
) t
ON t.CustomerID = cm.CustomerID AND t.Transaction_DT <= cm.LastTransaction_DT
WHERE (t.TransactionType BETWEEN 2 AND 4) AND
t.Channel < 4 AND
t.RowNo = 1


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bogus
Starting Member

41 Posts

Posted - 2014-11-18 : 21:30:48
quote:
Originally posted by tkizer

Try this in a test environment:

UPDATE cm
SET cm.PreviousTransaction_DT = t.Transaction_DT,
cm.PreviousTransactionType = t.TransactionType,
cm.PreviousTransactionChannel = t.Channel
FROM CustomerMaster cm
INNER JOIN
(
SELECT CustomerID, Transaction_DT, TransactionType, Channel, ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY Transaction_DT DESC AS RowNo
FROM [dbo].[vw_Purchase_Request_Transactions]
) t
ON t.CustomerID = cm.CustomerID AND t.Transaction_DT <= cm.LastTransaction_DT
WHERE (t.TransactionType BETWEEN 2 AND 4) AND
t.Channel < 4 AND
t.RowNo = 1

Switch it to a SELECT first to verify:

SELECT t.Transaction_DT, t.TransactionType, t.Channel
--UPDATE cm
--SET cm.PreviousTransaction_DT = t.Transaction_DT,
-- cm.PreviousTransactionType = t.TransactionType,
-- cm.PreviousTransactionChannel = t.Channel
FROM CustomerMaster cm
INNER JOIN
(
SELECT CustomerID, Transaction_DT, TransactionType, Channel, ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY Transaction_DT DESC AS RowNo
FROM [dbo].[vw_Purchase_Request_Transactions]
) t
ON t.CustomerID = cm.CustomerID AND t.Transaction_DT <= cm.LastTransaction_DT
WHERE (t.TransactionType BETWEEN 2 AND 4) AND
t.Channel < 4 AND
t.RowNo = 1


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



This looks GREAT! I will try first thing in the AM.

THANK YOU!!!

Is this a result of changes made since v2008? I swear that the original would have worked then...

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-19 : 11:47:21
quote:
Originally posted by bogus
Is this a result of changes made since v2008? I swear that the original would have worked then...



No. Your syntax wouldn't have worked in prior versions.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bogus
Starting Member

41 Posts

Posted - 2014-11-19 : 12:08:48
I need to do some heavy reading... all these new things!

THANK YOU!!!!

The step updates 1.8 million records in 21 seconds. That is an improvement from running 2 separate updates that would take 1.5~2 minutes each!! That is 21 SECONDS vs 3+ minutes! IMPRESSIVE.

Epic improvement. Next time you are in San Pedro, the beer is on me!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-19 : 12:35:51


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -