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
rajneesh1978Asked:
Who is Participating?
 
sachinpatil10dConnect With a Mentor Commented:
Explanation:
-- Recursive Common Table Expression (SplitRowsCTE)
WITH SplitRowsCTE ( Auditid, Qty, Quantity, RowIndex )
AS (
      -- Set: The root set of the recursive function.
      SELECT 
            Auditid, Qty, 1 AS Quantity, 1 AS RowIndex FROM Audit 
      -- Recursive Set: Iterate from the source until all rows have been generated.
      UNION ALL
      SELECT
            Auditid, Qty, 1 AS Quantity, RowIndex + 1 AS RowIndex FROM SplitRowsCTE
      -- stop just before we get to the max rows (root has already been created)
      WHERE RowIndex < Qty
)
-- Call the recursive SplitRowsCTE function
-- insert all output data in tmpTable table with one column added as id autoincrement column
SELECT Auditid, RowIndex, Quantity,ROW_NUMBER() over(order by auditid) id into tmpTable 
FROM SplitRowsCTE
ORDER BY Auditid, RowIndex;
 
-- Update the rows
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 the tmpTable
drop table tmpTable

Open in new window

0
 
VipulKadiaCommented:
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.
0
 
rajneesh1978Author Commented:
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.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
sachinpatil10dCommented:
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
0
 
rajneesh1978Author Commented:
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
0
 
sachinpatil10dCommented:
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

0
 
rajneesh1978Author Commented:
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
0
 
rajneesh1978Author Commented:
Thanks for your explanation.

Great work
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.