Complex Update Problem - SQL Server Forums

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!

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!

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!!

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/

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?

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/

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).

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/

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.

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/

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.

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/

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/

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...

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/

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!

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources