Associations in a multivalued database are technically considered to be a different table when interacted with from SQL. For example say you have an Invoice file called INV, with a DICT that looks like this.

DICT INV    08:16:43  08-01-16  Page 1

               Type &
Field......... Field. Field........ Conversion.. Column......... Output Depth &
Name.......... Number Definition... Code........ Heading........ Format Assoc..

@ID            D    0                            INV             10L    S
CustNum        D    1                                            5R     S
PartNum        D    2                                            8R     M LineI
                                                                        tem
Quantity       D    3                                            5R     M LineI
                                                                        tem

4 records listed.
>

You would not be able to use the command you are trying to update the PartNumber because you aren t specifying what @ID you want to the PartNumber to be associate with. For instance, you would not say...

UPDATE INV SET PartNumber = 123456;

..because it does not make sense and would fail the implicit foreign key constraint.

Additionally UPDATE might not be the command you want to use here, because if you have an invoice with an @ID of 123 and want to add a PartNumber and Quantity to it you would want to do something like this.

INSERT INTO INV (@ID, PartNum, Quantity) VALUES (123, 123456, 2);

From here you might expect that you can update the Quantity of INV 123, PartNum 123456 by issueing the command...

UPDATE INV SET Quantity = 7 WHERE @ID = 123 WHEN PartNum = 123456;

But if you were to try that Universe will complain that there is no Association called LineItem.

As far as UPDATES go, you need to have your DICT file really well organized to get it to work as expected. I would recommend you read the Universe SQL Reference guides (User, Ref and DBA). You can find those Here. In short, you need PH records in your DICTs for each MV Association and you will probably want to understand if your association is STABLE, UNSTABLE(Default) or has a KEY. But that is a bit deep for this exercise.

I added a PH record to my DICT for the Association LineItem.

DICT INV    08:38:16  08-01-16  Page 1

               Type &
Field......... Field. Field........ Conversion.. Column......... Output Depth &
Name.......... Number Definition... Code........ Heading........ Format Assoc..

@ID            D    0                            INV             10R    S
CustNum        D    1                                            5R     S
PartNum        D    2                                            8R     M LineI
                                                                        tem
Quantity       D    3                                            5R     M LineI
                                                                        tem
LineItem       PH     PartNum
                      Quantity

5 records listed.

And this now allows me to update the quantity as expected.

>UPDATE INV SET Quantity = 7 WHERE @ID = 123 WHEN PartNum = 123456;
UniVerse/SQL: 1 record updated.
>SELECT @ID, PartNum, Quantity FROM INV WHERE @ID = 123;
INV.......    PartNum.    Quantity

       123      123456           7

1 records listed.
>

I hope this helps.

Another way to update multivalue field is by invoking the UniVerse editor, ED command. For example, in TCL:

ED FILENAME RECORDID

To get detailed info on the Editor commands, type HELP in TCL.

Source: Guide to the UniVerse Editor.

Multivalue fields can be referred to in UniverseSQL using Dynamic Normalization.

The underscore is the separator between the main table and the multivalue related table in the normalized virtual table.

This query replaces values in an unassociated multivalue field:

UPDATE MAINTABLE_MULTIVALUE 
SET MULTIVALUE =  newvalue  
WHERE @ID =  keyvalue  AND MULTIVALUE =  oldvalue 

The associated fields can be referred to in associations:

UPDATE MAINTABLE_MULTVALUEASSOC 
SET ASSOCFIELD1 =  newvalue  
WHERE @ID =  keyvalue  AND ASSOCFIELD2 =  something 

The ordinal position of the multivalue to be replaced can be nominated in both associations and unassociated multivalue fields.

UPDATE MAINTABLE_MULTIVALUE 
SET MULTIVALUE =  newvalue  
WHERE @ID =  keyvalue  AND @ASSOC_ROW = 2

The associated fields are separated from the association with a dot in case of ambiguity (eg if the same fieldname is in multiple tables in the join). Universe is able to handle underscore and dots in all names as well as as separators provided there is no ambiguity in the full reference.

SELECT MAINTABLE_MULTIVALUEASSOC.ASSOCFIELD1
FROM MAINTABLE_MULTIVALUEASSOC
INNER JOIN someothertable ON etc

Note that the default key for both the main table and dynamically normalized related table, @ID, is always a string. The WHEN condition is not valid in Dynamically Normalized structures.