Geek Answers Handbook

Is there a good way to do this in SQL?

I am trying to solve the following problem completely in SQL (ANSI or TSQL, in Sybase ASE 12), without relying on cursors or looping rows by rows.

NOTE. I have already created a solution that performs the same task at the application level (therefore, please refrain from "answering" to "do not do this in SQL"), but in principle (and, hopefully, improved performance). I would like to know if there is an efficient (e.g., without cursors) pure SQL solution.

Customization

  • I have a table T with the following three columns (all NOT NULL):

    ---- Table T ----------------------------- | item | tag | value | | [int] | [varchar(10)] | [varchar(255)] |

  • The table has a unique index on item, tag

  • Each tag has the form of a string "TAG ##", where "##" is the number 1-99

  • Existing tags do not guarantee continuity, for example. Element 13 may be labeled “TAG1”, “TAG3”, “TAG10”.

  • TASK . I need to insert into the table several new rows from another T_NEW table, in which there are only elements and values, and assign them a new tag so that they do not violate the unique index on item, tag .

    The uniqueness of the values ​​does not matter (suppose the value of item + is always unique).

     ---- Table T_NEW -------------------------- | item | tag | value | | [int] | STARTS AS NULL | [varchar(255)] |

  • QUESTION How to assign new tags to all rows of the T_NEW table, for example:

    • All tag + tag combinations in the union of T and T_NEW are unique

    • Recently assigned tags must be in the form of "TAG ##"

    • Recently assigned tags should ideally be the smallest available for this element.

  • If this helps, you can assume that I already have a temporary #tags table, with a tag column that contains 99 rows containing all valid tags (TAG1..TAG99, one per line)

+4
source share
4 answers

I started fiddle , which will provide you with a list of available "open" tags by element. It does this with #tags (AllTags) and does outer-join-where-null . You can use this to insert new tags from T_New ...

 with T_openTags as ( select items.item, openTagName = a.tag from (select distinct item from T) items cross join AllTags a left outer join T on items.item = T.item and T.tag = a.tag where T.item is null ) select * from T_openTags

or see this updated script to do an update on the T_New table. Essentially adds row_number, so we can choose the right open tag to use in a single update statement. I have simplified sorting by tag names with a leading zero.

 with T_openTags as ( select items.item, openTagName = a.tag, rn = row_number() over(partition by items.item order by a.tag) from (select distinct item from T) items cross join AllTags a left outer join T on items.item = T.item and T.tag = a.tag where T.item is null ), T_New_numbered as ( select *, rn = row_number() over(partition by item order by value) from T_New ) update tnn set tag = openTagName from T_New_numbered tnn inner join T_openTags tot on tot.item = tnn.item and tot.rn = tnn.rn select * from T_New

updated fiddle with poor row_number string replacement that only works with different T_New values

0
source

Try the following:

 DECLARE @T TABLE (ITEM INT, TAG VARCHAR(10), VALUE VARCHAR(255)) INSERT INTO @T VALUES (1,'TAG1', '100'), (2,'TAG2', '200') DECLARE @T_NEW TABLE (ITEM INT, TAG VARCHAR(10), VALUE VARCHAR(255)) INSERT INTO @T_NEW VALUES (3,NULL, '500'), (4,NULL, '600') INSERT INTO @T SELECT ITEM, ('TAG' + CONVERT(VARCHAR(20),ITEM)) AS TAG, VALUE FROM @T_NEW SELECT * FROM @T
0
source

Insert directly into t :

 INSERT INTO t (item, tag, value) SELECT item, ( SELECT MIN(tags.tag) FROM #tags AS tags LEFT JOIN t AS o ON tags.tag = o.tag AND o.item_id = n.item_id WHERE o.tag IS NULL ) AS tag, value FROM t_new AS n ;

t_new update:

 UPDATE t_new AS n SET tag = ( SELECT MIN(tags.tag) FROM #tags AS tags LEFT JOIN t AS o ON tags.tag = o.tag AND o.item_id = n.item_id WHERE o.tag IS NULL ) ;

hit>

Correction

 UPDATE n SET n.tag = w.tag FROM ( SELECT item_id, tag, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY value) AS rn FROM t_new ) AS n JOIN ( SELECT di.item_id, tags.tag, ROW_NUMBER() OVER (PARTITION BY di.item_id ORDER BY tags.tag) AS rn FROM ( SELECT DISTINCT item_id FROM t_new ) AS di CROSS JOIN #tags AS tags LEFT JOIN t AS o ON tags.tag = o.tag AND o.item_id = di.item_id WHERE o.tag IS NULL ) AS w ON w.item_id = n.item_id AND w.rn = n.rn ;
-1
source

OK, here is the right solution tested to work with Sybase (H / T: thanks a lot @ypercube for providing a solid foundation for it)

 declare @c int select @c = 1 WHILE (@c > 0) BEGIN UPDATE t_new SET tag = ( SELECT min(tags.tag) FROM #tags tags LEFT JOIN to ON tags.tag = o.tag AND o.item = t_new.item LEFT JOIN t_new n3 ON tags.tag = n3.tag AND n3.item = t_new.item WHERE o.tag IS NULL AND n3.tag IS NULL ) WHERE tag IS NULL -- and here the main magic for only updating one item at a time AND NOT EXISTS (SELECT 1 FROM t_new n2 WHERE t_new.value > n2.value and n2.tag IS NULL and n2.item=t_new.item) SELECT @c = @@rowcount END
-1
source

More articles:


All Articles