I have a DICT that is set as a multivalue. When opening TCL, and doing an UPDATE DICT.FILE SET Blah = 'Y' statement, I get the following error:
Correlatives are illegal for UPDATE, column "Blah"
How can I update a specific attribute in a multivalue field?
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.
