Best Practice to Update EAV table rows

Question

I have the following table:

SubjectID AttributeID ValueID
1         1           2
1         1           3
1         2           1
2         1           3
2         2           1
1         3           1

An attribute can have multiple values (multiple appearances in the above table for the same attribute). There is no constraint of how many appearances for the same attribute (different value).

I wan't to Update the Subject with SubjectID=1, to change the ValueID to only 1 where the AttributeID is 1, so

Before:

Select * from Subject WHERE SubjectID=1 AND AttributeID=1
--returns:
SubjectID AttributeID ValueID
1         1           2
1         1           3

After:

Select * from Subject WHERE SubjectID=1 AND AttributeID=1
--returns:
SubjectID AttributeID ValueID
1         1           1

I am doing this with a stored procedure with optional parameters (all null and update only the attributes that were provided), now this is not an issue. My question is:

What is the best practice to update this rows? I see the following answers as viable:

Delete all the rows that contain the specified attribute, then insert the new ones;
If there is only one attribute of that type (for the specified subject) update that one (not a good solution if there are more than 1 for the same attribute)

Any other ideas?

Are you using the multiple values for each subject-attribute pair elsewhere? If not, I'd recommend removing these and updating the table design to prevent re-occurance. This would simplify *all* the queries/SPs you write on this table. If you need to keep these records then yes, I would delete then insert - within a transaction. — David Rushton, Apr 01 '17 at 08:19
I don't really have a choice, I had to implement this design, almost done, only stuck on updates o.O — Norbert Forgacs, Apr 01 '17 at 08:58

score 2 · Answer 1 · answered Apr 01 '17 at 08:18

2

You could update just one row and then delete the others like so:

set rowcount 1;

update Subject
   set ValuedID = 1
 where SubjectID = 1
   and AttributeID = 1;

set rowcount 0;

delete Subject
 where SubjectID = 1
   and AttributeID = 1
   and ValuedID <> 1;

answered Apr 01 '17 at 08:18

IngoB

2,552
1
20
35

I'm not sure this will work. The update isn't limited to a row. From the example Subject-Attribute-Value 1, 1, 3 and 1, 1, 2 would both be updated to 1, 1, 1. The delete would not find any records. This would leave the OP with 2 records, instead of the requested 1. – David Rushton Apr 01 '17 at 08:21
You can [limit updates](http://stackoverflow.com/questions/3860975/sql-update-top1-row-query), via a CTE, to a set row count. – David Rushton Apr 01 '17 at 08:22
My current implementation is, delete one by one and insert one by one, I am wondering if changing to this implementation will give better performance, I think it reduces the task with one query, instead of deleting n rows you delete n-1 – Norbert Forgacs Apr 01 '17 at 09:01
Sorry IngoB! I completely missed the row count statements. Must be time for more coffee. Nice solution. – David Rushton Apr 01 '17 at 10:27
Using [**`set rowcount`** is deprecated](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql), use `top (n)` instead. – SqlZim Apr 01 '17 at 12:14

score 1 · Answer 2 · answered Apr 01 '17 at 12:18

Using set rowcount is deprecated, use top (n) instead.

Important

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax. For more information, see TOP (Transact-SQL).

update top (1) Subject
  set ValueID = 1
where SubjectID = 1
  and AttributeID = 1;

delete Subject
where SubjectID = 1
  and AttributeID = 1
  and ValueID <> 1;

rextester demo: http://rextester.com/ATDKI87027

returns:

+-----------+-------------+---------+
| SubjectID | AttributeID | ValueID |
+-----------+-------------+---------+
|         1 |           1 |       1 |
|         1 |           2 |       1 |
|         2 |           1 |       3 |
|         2 |           2 |       1 |
|         1 |           3 |       1 |
+-----------+-------------+---------+

Best Practice to Update EAV table rows

2 Answers2