Update query problem

[lambic]

Hi,

I am trying to write an update query without much success! I have the following 2 tables:

TABLE_A
Account_ID
Sub_Account_ID
Sub_Account_Adj
Issue_Flag

TABLE_B
Account_ID

What I need to do is set the Issue_Required_Flag from 0 to 1 in TABLE_A where the Account_ID exists in TABLE_B, but only on the latest record in TABLE_A.

For example, assume TABLE_A contains the following records for a partcular account:

Account_ID Sub_Account_ID Sub_Account_Adj Issue_Flag
11111111 1 1 0
11111111 1 2 0
11111111 2 1 0
11111111 2 2 0
11111111 2 3 0

and assuming Account_ID 11111111 exists in TABLE_B, I would want to set the Issue_Flag to 1 for the record:

Account_ID Sub_Account_ID Sub_Account_Adj Issue_Flag
11111111 2 3 0

Apologies for the rather long-winded description, but I seem to be going round in circles!

Many thanks for any help.

 

[LKBrwnDBA]

Try this:

UPDATE TABLE_A A
   SET Issue_Flag = '1'
 WHERE EXISTS (
    SELECT 1 FROM TABLE_B B
     WHERE B.Account_Id = A.Account_Id)
   AND (Sub_Account_ID, Sub_Account_Ad) = (
    SELECT MAX(Sub_Account_ID), MAX(Sub_Account_Ad) 
      FROM TABLE_A X
     WHERE X.Account_Id = A.Account_Id);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[r937]

this isn't necessarily going to work --

AND (Sub_Account_ID, Sub_Account_Ad) = (
SELECT MAX(Sub_Account_ID), MAX(Sub_Account_Ad)

because those two MAXes could come from different rows



r937.com | rudy.ca

 

[LKBrwnDBA]

Ooops, true...But then it would depend on the DBMS:

For M$SQL/MySQL/SYBASE:

UPDATE TABLE_A A
   SET Issue_Flag = '1'
 WHERE EXISTS (
    SELECT 1 FROM TABLE_B B
     WHERE B.Account_Id = A.Account_Id)
   AND (Sub_Account_ID, Sub_Account_Ad) = (
    SELECT TOP 1 Sub_Account_ID, Sub_Account_Ad 
      FROM TABLE_A X
     WHERE X.Account_Id = A.Account_Id
     ORDER BY Sub_Account_ID DESC, Sub_Account_Ad DESC);

For Oracle:

UPDATE TABLE_A A
   SET Issue_Flag = '1'
 WHERE EXISTS (
    SELECT 1 FROM TABLE_B B
     WHERE B.Account_Id = A.Account_Id)
   AND (Sub_Account_ID, Sub_Account_Ad) = (
    SELECT * FROM (
      SELECT Sub_Account_ID, Sub_Account_Ad
           , ROW_NUMBER() OVER(ORDER BY Sub_Account_ID DESC, Sub_Account_Ad DESC) Rk
        FROM TABLE_A X WHERE X.Account_Id = A.Account_Id)
     WHERE rk = 1);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[r937]

what about for ANSI SQL (which is what this forum is)?

by the way, mysql doesn't understand TOP

r937.com | rudy.ca

 

[LKBrwnDBA]

MySQL does'nt? -- Too bad.

Dont know the ANSI SQL for this, do you?




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Ok, here is the MySQL solution:

UPDATE TABLE_A A
   SET Issue_Flag = '1'
 WHERE EXISTS (
    SELECT 1 FROM TABLE_B B
     WHERE B.Account_Id = A.Account_Id)
   AND (Sub_Account_ID, Sub_Account_Ad) = (
    SELECT Sub_Account_ID, Sub_Account_Ad 
      FROM TABLE_A X
     WHERE X.Account_Id = A.Account_Id
     ORDER BY Sub_Account_ID DESC, Sub_Account_Ad DESC
     LIMIT 1);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[dnoeth]

Using SQL:1999 or SQL:2003

UPDATE TABLE_A A
   SET Issue_Flag = '1'
WHERE
  EXISTS
   (
    SELECT * FROM TABLE_B B
    WHERE B.Account_Id = A.Account_Id
   )
AND 1 =
 (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY Account_ID
                     ORDER BY Sub_Account_ID DESC,
                              Sub_Account_Adj DESC)
  FROM TABLE_A X
  WHERE X.Account_Id = A.Account_Id
 );

Or probably more efficient:

UPDATE TABLE_A A
   SET Issue_Flag = '1'
WHERE (Account_Id, Sub_Account_ID, Sub_Account_Adj) IN
 (
  SELECT
    A.Account_Id, A.Sub_Account_ID, A.Sub_Account_Adj
  FROM
   (
    SELECT
      Account_Id, Sub_Account_ID, Sub_Account_Adj,
      ROW_NUMBER() OVER (PARTITION BY Account_ID
                       ORDER BY Sub_Account_ID DESC,
                                Sub_Account_Adj DESC) AS rn
    FROM TABLE_A
   ) AS A
  JOIN TABLE_B AS B ON B.Account_Id = A.Account_Id
  WHERE rn = 1
 );

Dieter

 

[lambic]

Have managed to sort it out - with your help.

Thanks for taking the time out to help guys.

Cheers!