• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

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
0
rajneesh1978
Asked:
rajneesh1978
  • 4
  • 3
1 Solution
 
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

Great work
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now