Multiple Row Update

[uncgis]

I need to update sql statement with multiple rows. For example, the primary keys are Major and Study. The second column is a Value.

Primary Value
Major 10
Major 20
Study 01
Study 02

I need to keep the Primary Keys the same but change the Study Value field to the same as the Major Value

Primary Value
Major 10
Major 20
Study 10
Study 20

Here is the SQL I wrote

UPDATE SORXREF a
SET (a.SORXREF_EDI_VALUE) = (
SELECT DISTINCT b.SORXREF_EDI_VALUE
FROM SORXREF b
WHERE b.SORXREF_XLBL_CODE = 'STVMAJR')
/

Then I get the following error: single-row subquery returns more than one row

 

[Catadmin]

First, do a backup in case you need to restore.

Second, try eliminating the Distinct word from your subquery. That's where the "single-row" part of your error is coming from.

Does that help?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[uncgis]

thanks for responding...

I have tried to take out the distinct but still the same error

 

[Catadmin]

Are the field names for the Study value the same as for the major value? If not, I would just run it as follows:

Update SORXREF
Set Studyval = majorval


This should update every record of the table for the StudyVal to equal the MajorVal of the record it is on.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[uncgis]

yeah....the are the same name: sorxref_edi_value

thanks

 

[Catadmin]

Then try adding an identity column to the table so you can tell the various "Major" and "Study" records apart. Increment it by one, do your update without the distinct (and you don't have to have an alias in your subquery, SQL will know what you're trying to do) and adding the following to your WHERE clause

"and [identitycolumn] < (select [identitycolumn] from Sorxref)"

You'll have to play with this code a little. Sometimes what should be a 'less than' ends up working as a 'greater than'. If you want some practice, do a Select Into on your table to create a new table, then test the code on the copied table so you don't disrupt anyone. But you'll have to do something to make the individual records unique enough for your update to work since the study records and the major records use the same field.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[dbomrrsm]

how do you know which study and major are associated - and also is the study always a tenth of the major - if so do

update yourtable
set study = study * 10

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]