asked on

SQL server

I have below table structure

Table name : Serialno
Serialno varchar(10) primary key
Auditid int allow null

Table name : Audit
Auditid Identity Yes
Qty int

You can refer to attached excel including data and output required

I would like to extract the serial no on the basis of qty in audit table and update back the extracted serial no with the same auditid from audit table

I want to update the serialno. table without doing cursors/loops

Thanks

Sampledata.xlsx

VipulKadia

I saw your file. But I am not clear about on what condition you want to update your table? I mean what values to be updated for Serial no? Please explain it.

rajneesh1978

ASKER

Against serial no we want to update audit id.

1) Extract the no. of serial no on the basis of Qty in audit table
2) Update the Audit id in serialno table as per the qty extracted

for example 3 qty for audit id 2 so we need to update randomaly any 2 serial numbers with 2 audit id.
I have included the output sheet for your reference in excel. Please check for your clear understanding.

sachinpatil10d

can you please expain logic behind some serial no having null and sum having auditid

10HF0135KMYJRJKUCNPG      NULL
10HF013DQCSE7X755TT9      4
10HF013DSK3D7596CM6P      NULL
10HF013SSQ4P3SU99DFG      4
10HF0143DVW6SJX773NR      4
10HF014EHDPPGRUQ3XWS      1
10HF01597M6WP55DDJD7      1
10HF015EAFSNGNNCXFK5      NULL
10HF015FEV6TX74T4TV6      2
10HF016D9JXYQCMYRRXQ      2
10HF016ETMVEUAQCQGSH      NULL
10HF016J93NPKUQWWXUC      NULL
10HF016KDCRA6DNCM9X6      2
10HF016TS6HX9JWSP9VR      NULL
10HF0177HNM5QU3KJJMW      3
10HF017CATKAXKMYYNCP      NULL
10HF017D7JS47GCVHYYX      3
10HF017JHYSVEWXQ3RC6      4
10HF017T5GK7RCJTX6P6      3
10HF017XPV5V97J7T3GM      3

rajneesh1978

ASKER

Serial number table having pregenerated serialnumbers and auditid is null

Auditid will update when we want to extract serial number on the basis of requirement and map back the serial numbers extracted for which auditid.

Thanks

sachinpatil10d

not possible in one go
try this

WITH SplitRowsCTE ( Auditid, Qty, Quantity, RowIndex )
AS (
      SELECT 
            Auditid, Qty, 1 AS Quantity, 1 AS RowIndex FROM Audit 
      UNION ALL
      SELECT
            Auditid, Qty, 1 AS Quantity, RowIndex + 1 AS RowIndex FROM SplitRowsCTE
      WHERE RowIndex < Qty
)
SELECT Auditid, RowIndex, Quantity,ROW_NUMBER() over(order by auditid) id into tmpTable 
FROM SplitRowsCTE
ORDER BY Auditid, RowIndex;
 
update Serialno set auditid = tmpTable.Auditid from 
tmpTable inner join (select * ,ROW_NUMBER() over(order by [serial no]) id from Serialno)t2
on t2.id = tmpTable.id inner join Serialno on Serialno.[serial no] = t2.[serial no]

drop table tmpTable

Open in new window

rajneesh1978

ASKER

Thanks a lot. Can you please explain me the logic and details of the query. How you created this. and what is the below part

SELECT
Auditid, Qty, 1 AS Quantity, 1 AS RowIndex FROM Audit
UNION ALL
SELECT
Auditid, Qty, 1 AS Quantity, RowIndex + 1 AS RowIndex FROM SplitRowsCTE
WHERE RowIndex < Qty

ASKER CERTIFIED SOLUTION

sachinpatil10d

membership

This solution is only available to members.

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

Start Free Trial

rajneesh1978

ASKER

Thanks for your explanation.

Great work