Update sql table rows by a set of value return by subquery

Question

I want to update the table IVRecords set IVNO = value return by a subquery

While (Select Count(*) From [IVRecords] Where IVNO= '22644') > 0
Begin

    update [IVRecords] set IVNO= (Select  a.IVNO from [DVHead] a  inner join [IVRecords] b on a.DELIVERYNO=b.DELIVERYNO)

End

But the subquery return multiple value : 254,325,25346,25346 I have over 58000 records

So how to update the table IVRecords with the value return by subquery Select a.IVNO from [DVHead] a inner join [IVRecords] b on a.DELIVERYNO=b.DELIVERYNO) one by one?

The IVNo of IVRecords has the duplicated value 22644 caused by making mistake updation. And the IVNO of DVHead is the right IVNO of IVRecords. So I want to copy them to the IVRecords that has the wrong IVNo. 02 tables have key DELIVERYNO. My expected result: Before:

IVRecord     
DELIVERYNO   IVNO 
253           22664
253          22664
456          22664
789          22664

DVHead
DELIVERYNO   IVNO
253          1234
456          5236
789           6478
Expected result

IVRecord     
DELIVERYNO   IVNO 
253           1234
253          1234
456          5236
789          6478

What are you actually trying to do here?

holder
– holder

2018-04-24 08:50:00 +00:00
Commented Apr 24, 2018 at 8:50 — holder
– holder, Commented Apr 24, 2018 at 8:50
please see my edited post

Cát Tường Vy
– Cát Tường Vy

2018-04-24 09:05:42 +00:00
Commented Apr 24, 2018 at 9:05 — Cát Tường Vy
– Cát Tường Vy, Commented Apr 24, 2018 at 9:05

Radu Gheorghiu · Accepted Answer · 2018-04-24 09:17:20Z

2

I think this would be an equivalent set-based version of your code:

update ivr
    set ivr.ivno = a.ivno
from IVRecords ivr
    inner join DVHead a
        on ivr.deliveryNO = a.deliveryno
where ivr.ivno = '22664'

This should run much faster than the row-by-row (one by one) version in your question.

Also, here's a SQLFiddle with how the code works.

(Although, if something's not right, please post an example of what data you have and the expected output)

edited Apr 24, 2018 at 9:17

answered Apr 24, 2018 at 8:50

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

holder Over a year ago

Is it really? To me it looks like he/she wants to update the IVRecords table as long as there is a invo= 22264 record in the table. Confusing indeed...

Radu Gheorghiu Over a year ago

@user3035133 Then please post an example of what data you have now and what is the expected output.

Radu Gheorghiu Over a year ago

@user3035133 There was a slight typo in my query. Please have a look now.

Cát Tường Vy Over a year ago

Only 2 rows affected. I have 58045 rows need to be updated

Radu Gheorghiu Over a year ago

It might be the condition WHERE ivr.ivno = 22664. But that is part of the original question, which is why I added it.

|

Collectives™ on Stack Overflow

Update sql table rows by a set of value return by subquery

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related