CODEWITHSUNDEEP
sql-servert-sql
user1059110
user1059110

Reputation: 129

SQL Server > copy certain rows and change 2 values

I'm selecting my data with the following statement:

select distinct min(revision_number) as revno,po_number 
from PO_HEADER 
group by PO_NUMBER
having MIN(revision_number) > 0;

I get the following data:

7   30492-SA-EH504
20  30492-PS-SO895
20  30492-RA-DD219
26  30492-SA-KK474
1   30492-BA-AT236
17  30492-RA-DD386
21  30492-PS-FS803
25  30492-PS-WM324
33  30492-SA-NS011

I want to copy the row with the min(revno) and set it's revno to ZERO. I've tried insert into using the entire table and using where exists and my select statement above and it copies the entire table.

This is what the table for 30492-SA-EH504 current looks like:

30492-SA-EH504 7
30492-SA-EH504 8
30492-SA-EH504 9
30492-SA-EH504 10

Here's how I would like it to look after:

30492-SA-EH504 0
30492-SA-EH504 7
30492-SA-EH504 8
30492-SA-EH504 9
30492-SA-EH504 10

I was browsing the MSDN for T-SQL to figure out how to use exists in this case, but as I said it either copies everything or nothing the way I am failing to do it currently.

Upvotes: 1

Views: 343

Answers (2)

MartW
MartW

Reputation: 12538

insert into PO_HEADER(po_number, revision_number, ....other columns)
    select POH.po_number, 0, ...other columns
    from PO_Header AS POH
    INNER JOIN (select po_number, MIN(revision_number) as min_revision_number
        from PO_HEADER 
        group by PO_NUMBER
        having MIN(revision_number) > 0) AS POHMin ON POHMin.po_number = POH.po_number
            AND POHMin.min_revision_number = POH.revision_number

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460138

This should work as expected:

WITH CTE AS(
    SELECT RevNum = ROW_NUMBER()OVER(PARTITION BY po_number ORDER BY revision_number)
         , revision_number
         , po_number
    FROM PO_HEADER t
) 
INSERT INTO PO_HEADER 
SELECT 0, t1.po_number
FROM CTE t1
WHERE t1.RevNum = 1
AND EXISTS(
    SELECT  NULL FROM CTE t2
    WHERE   t2.po_number = t1.po_number
    AND     t2.RevNum > 1
)

Upvotes: 1

Related Questions