asked on

Help with SQL Query. Updates to 2 tables at the same time.

What I am doing is whenever I have to update the records in TABLE A based on a given condition,I need to update table B with a ACTIVATION_STATUS_ID.

ie; I have to flag SERIAL_PACK_NUMBER in TABLE A and set its value to 1 and the FLAG_COUNT Value in incremental order. Once that is done, I update the TABLE B and set the ACTIVATION_STATUS_ID to 1 (initial default value is 0)

The table A will have duplicate entries for SERIAL_PACK_NUM.

So, here is my code,

BEGIN
UPDATE TABLE A
SET
      FLAG_STATUS_ID=1,
      FLAG_COUNT=(I want to get the next seq value using count(*) when ever there is a SERIAL_PACK_NUM is already in the table row, It should be 0 if the SERIAL_PACK_NUM is not there otherwise, it should be reflecting the number of time the SERIAL_PACK_NUM is there in the system).
WHERE
      <conditions. There is no SERIAL_PACK_NUM in the condition>
END

BEGIN
UPDATE TABLE B
SET
      ACTIVATION_STATUS_ID
WHERE
      SERIAL_PACK_NUMB IN (SELECT SERIAL_PACK_NUMB FROM TABLE A WHERE SERIAL_PACK_NUMB=@SERIAL_PACK_NUMB)
END

Any help in this regards, greatly appreciated. I am using SQL Server 2008R2.

Raja Jegan R

Hope this is what you require..

DECLARE @SERIAL_PACK_NUMB int

UPDATE TABLEA
SET
      FLAG_STATUS_ID=1,
      FLAG_COUNT=ISNULL((SELECT count(*) FROM TABLEA WHERE SERIAL_PACK_NUM = @SERIAL_PACK_NUMB),0) + 1

UPDATE TABLEB
SET ACTIVATION_STATUS_ID = 1
WHERE SERIAL_PACK_NUMB IN (SELECT SERIAL_PACK_NUMB FROM TABLEA WHERE SERIAL_PACK_NUMB=@SERIAL_PACK_NUMB)

Open in new window

Member_2_7967119

ASKER

@SERIAL_PACK_NUMB is not provided as parameter. I made a mistake in TABLE 2.

it should be

BEGIN
UPDATE TABLE B
SET
ACTIVATION_STATUS_ID
WHERE
SERIAL_PACK_NUMB IN (SELECT SERIAL_PACK_NUMB FROM TABLE A WHERE <conditions. There is no SERIAL_PACK_NUM in the condition>)
END

Member_2_7967119

ASKER

I use the same where clause as used in the Update for table A.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_7967119

ASKER

Thank you very much.

Raja Jegan R

Welcome..