SQL-запрос с подстрокой charindex

table1
::::::::::::::::::::::::::::::::::
id | id_data | id_t | value 
1  | 43      | 1    | 
2  | 46      | 1    | 111,112,113
3  | 43      | 2    | 
4  | 46      | 2    | 90,5

table2
:::::::::::::::::::::::::::::::::::
id_value | cat
112      | cat1
5        | cat2

Привет, мне нужна помощь здесь, если это возможно, пожалуйста.

Мне нужно обновить table1.value, где id_data равно 43, с помощью table2.cat, где id_value = цифры после значения ',' 'до следующего',' если они есть для каждой группы в 'id_t'

Я попытался с помощью простого запроса, но он возвращает некоторый нуль, но «значение» не может быть нулевым.

update table1 
set value = (select cat from table2 
            where convert(nvarchar,id_value) = substring(value,5,3))
where id_data='43'

Я пытался включить CHARINDEX, чтобы взять из «,», но я просто не могу понять, как это работает.

В идеале это должно выглядеть так:

::::::::::::::::::::::::::::::::::
id | id_data | id_t | value 
1  | 43      | 1    | cat1
2  | 46      | 1    | 111,112,113
3  | 43      | 2    | cat2
4  | 46      | 2    | 90,5

Может ли кто-нибудь указать мне правильное направление, пожалуйста?

Я думаю, это просто.. но я все еще учусь...

заранее спасибо.

::::::::::::::::

ОБНОВЛЕНИЕ1

WITH UpdateableCTE AS
(
    SELECT t1.id
          ,t1.id_data
          ,t1.id_t
          ,SecondNr
          ,(
            SELECT t2.cat
            FROM @table2 AS t2 WHERE t2.id_value=SecondNr
           ) AS NewCat
           ,t1.value
    FROM @table1 AS t1
    OUTER APPLY(SELECT CAST('<x>' + REPLACE(x.value,',','</x><x>') + '</x>' AS XML).value('/x[2]','int')
                FROM @table1 AS x 
                WHERE x.id_t=t1.id_t AND x.value IS NOT NULL AND id_data='46') AS ID(SecondNr)
    WHERE t1.value IS NULL
)
UPDATE UpdateableCTE SET value=NewCat;
--somehow where id_data='43'

Я оставлю здесь одну таблицу, которая больше похожа на настоящую со всеми id_data и полями:

17974492    1   999251  somevalue
17974493    2   999251  somevalue
17974494    3   999251  somevalue
17974495    4   999251  somevalue
17974496    5   999251  somevalue
17974497    43  999251  (thishsouldbeupdated)
17974498    6   999251  somevalue
17974499    7   999251  somevalue
17974500    46  999251  111,311
17974501    8   999251  somevalue
17974502    9   999251  somevalue
17974503    10  999251  somevalue
17974504    11  999251  somevalue
17974505    12  999251  somevalue
17974506    13  999251  somevalue
17974507    1   999252  somevalue
17974508    2   999252  somevalue
17974509    3   999252  somevalue
17974510    4   999252  somevalue
17974511    5   999252  somevalue
17974512    43  999252  (thisshouldbeupdated)
17974513    6   999252  somevalue
17974514    7   999252  somevalue
17974515    46  999252  98,98
17974516    8   999252  somevalue
17974517    9   999252  somevalue
17974518    10  999252  somevalue
17974519    11  999252  somevalue
17974520    12  999252  somevalue
17974521    13  999252  somevalue

sql sql-server substring charindex
person vvic    schedule 14.06.2017    source источник
comment
Общий совет: Не храните данные CSV в таблицах базы данных. Это означает, что ваши данные не нормализованы, и с ними обычно сложно работать.   -  person Tim Biegeleisen    schedule 14.06.2017
comment
Извините, я не понимаю вашей логики... Есть ли cat1, потому что в следующей строке с id_data=46 есть 112 где-то между запятыми, а вы установили cat2, потому что в следующей строке есть 5? Это странно и ужасно пахнет...   -  person Shnugo    schedule 14.06.2017
comment
id_t группирует их в первой таблице, и каждая «группа» имеет запись id_data 46, где один идентификатор, который должен присоединиться к каждому id_t, равен (вторая запись после «,»). Мне нужно обновить id_data 43 для каждого id_t   -  person vvic    schedule 14.06.2017
comment
@Shnugo Читая ваш комментарий еще раз, я думаю, что теперь понимаю, что вы имеете в виду, извините. Действительно, таблица1 и таблица2 соединяются второй записью между запятыми в таблице1. Я знаю, что это немного странно, но я работаю над уже определенной таблицей, которую сейчас нельзя изменить.   -  person vvic    schedule 14.06.2017
comment
@vvic, это плохо... Что произойдет, если в вашей первой таблице будет 111,112,5,113? Вы бы взяли cat, cat2 или оба? Всегда ли на id_t приходится ровно две строки? Какова цель id_data? Находится ли столбец id в строгом порядке (идентификатор строки с номерами CSV всегда будет id+1)?   -  person Shnugo    schedule 14.06.2017
comment
@Shnugo Если у него есть 111,112,5,113, он все равно должен принимать cat1, так как вторая часть строки (112) - единственная, которая мне нужна. Вот почему я подумал, что должен использовать charindex для извлечения второй части строки. На idt больше строк, это просто пример для упрощения, но это текущее обновление в любом случае не должно касаться других. id_data определяет, какой тип данных находится внутри, например: 43=category, 46(from, to ,)= id of the category, конечный пользователь — это тот, кто пишет идентификатор категории в 46. Да, id — это автонумер. Спасибо за ваше время.   -  person vvic    schedule 14.06.2017
comment
@vvic Хорошо, я думаю, теперь я понял... В этом ответе вы найдете простой и типобезопасный подход (возьмите просто самая первая строка!) чтобы получить число из списка. Попробуйте изменить мой ответ ниже, чтобы прочитать соответствующее значение cat. Если вам нужна помощь, просто позвоните...   -  person Shnugo    schedule 14.06.2017

Ответы (1)


arrow_upward
0
arrow_downward

Это не чисто, и я бы не рекомендовал это, но вы можете найти помощь:

DECLARE @table1 TABLE(id INT,id_data INT,id_t INT,value VARCHAR(100));
INSERT INTO @table1 VALUES 
 (1,43,1,NULL)
,(2,46,1,'111,112,113')
,(3,43,2,NULL)
,(4,46,2,'90,5')

DECLARE @table2 TABLE(id_value INT,cat VARCHAR(100));
INSERT INTO @table2 VALUES
 (112,'cat1')
,(5,'cat2');

SELECT t1.id
      ,t1.id_data
      ,t1.id_t
      ,ID.List
      ,(
        SELECT t2.cat
        FROM @table2 AS t2 WHERE CHARINDEX(',' + CAST(t2.id_value AS VARCHAR(100)) + ',',',' + ID.List + ',')>0
       )
FROM @table1 AS t1
OUTER APPLY(SELECT x.value FROM @table1 AS x WHERE x.id_t=t1.id_t AND x.value IS NOT NULL) AS ID(List)
WHERE t1.value IS NULL

ОБНОВЛЕНИЕ: Ваше объяснение относительно использования второго числа в качестве обновления

Попробуй это

DECLARE @table1 TABLE(id INT,id_data INT,id_t INT,value VARCHAR(100));
INSERT INTO @table1 VALUES 
 (1,43,1,NULL)
,(2,46,1,'111,112,113')
,(3,43,2,NULL)
,(4,46,2,'90,5')

DECLARE @table2 TABLE(id_value INT,cat VARCHAR(100));
INSERT INTO @table2 VALUES
 (112,'cat1')
,(5,'cat2');

WITH UpdateableCTE AS
(
    SELECT t1.id
          ,t1.id_data
          ,t1.id_t
          ,SecondNr
          ,(
            SELECT t2.cat
            FROM @table2 AS t2 WHERE t2.id_value=SecondNr
           ) AS NewCat
           ,t1.value
    FROM @table1 AS t1
    OUTER APPLY(SELECT CAST('<x>' + REPLACE(x.value,',','</x><x>') + '</x>' AS XML).value('/x[2]','int')
                FROM @table1 AS x 
                WHERE x.id_t=t1.id_t AND x.value IS NOT NULL) AS ID(SecondNr)
    WHERE t1.value IS NULL
)
UPDATE UpdateableCTE SET value=NewCat;

SELECT * FROM @table1

Концепция заключается в обновляемом CTE, где вы можете использовать обычный SELECT для получения нужных вам значений. Затем вы можете напрямую обновить производную таблицу (если затронуты только столбцы одной таблицы).

OUTER APPLY использует трюк с XML, чтобы разделить список CSV, чтобы прочитать второе число.

ОБНОВЛЕНИЕ 2: используйте новые образцы данных

Далее будут использоваться новые образцы данных и работать с идентификаторами:

DECLARE @table1 TABLE(id INT,id_data INT,id_t INT,value VARCHAR(100));
INSERT INTO @table1 VALUES 
 (17974492,1,999251,'somevalue')
,(17974493,2,999251,'somevalue')
,(17974494,3,999251,'somevalue')
,(17974495,4,999251,'somevalue')
,(17974496,5,999251,'somevalue')
,(17974497,43,999251,'(thishsouldbeupdated)')
,(17974498,6,999251,'somevalue')
,(17974499,7,999251,'somevalue')
,(17974500,46,999251,'111,311')
,(17974501,8,999251,'somevalue')
,(17974502,9,999251,'somevalue')
,(17974503,10,999251,'somevalue')
,(17974504,11,999251,'somevalue')
,(17974505,12,999251,'somevalue')
,(17974506,13,999251,'somevalue')
,(17974507,1,999252,'somevalue')
,(17974508,2,999252,'somevalue')
,(17974509,3,999252,'somevalue')
,(17974510,4,999252,'somevalue')
,(17974511,5,999252,'somevalue')
,(17974512,43,999252,'(thisshouldbeupdated)')
,(17974513,6,999252,'somevalue')
,(17974514,7,999252,'somevalue')
,(17974515,46,999252,'98,98')
,(17974516,8,999252,'somevalue')
,(17974517,9,999252,'somevalue')
,(17974518,10,999252,'somevalue')
,(17974519,11,999252,'somevalue')
,(17974520,12,999252,'somevalue')
,(17974521,13,999252,'somevalue');

DECLARE @table2 TABLE(id_value INT,cat VARCHAR(100));
INSERT INTO @table2 VALUES
 (311,'cat1')
,(98,'cat2');

WITH UpdateableCTE AS
(
    SELECT t1.id
          ,t1.id_data
          ,t1.id_t
          ,SecondNr
          ,(
            SELECT t2.cat
            FROM @table2 AS t2 WHERE t2.id_value=SecondNr
           ) AS NewCat
           ,t1.value
    FROM @table1 AS t1
    OUTER APPLY(SELECT CAST('<x>' + REPLACE(x.value,',','</x><x>') + '</x>' AS XML).value('/x[2]','int')
                FROM @table1 AS x 
                WHERE x.id_t=t1.id_t AND x.id_data=46) AS ID(SecondNr)
    WHERE t1.id_data=43
)
UPDATE UpdateableCTE SET value=NewCat;

SELECT * FROM @table1;
person Shnugo    schedule 14.06.2017
comment
Я думаю, ты потерял меня там... Прости. Итак, запрос на обновление будет примерно таким? update table1 set value = ( SELECT cat FROM table2 WHERE CHARINDEX(',' + CAST(id_value AS VARCHAR(100)) + ',',',' + ID.List + ',')>0 ) from table1 OUTER APPLY(SELECT x.value FROM table1 AS x WHERE x.Id_t=t1.Id_t AND x.value IS NOT NULL) AS ID(List) where Id_data='43' - person vvic; 15.06.2017
comment
Я тестировал его, и он почти готов, трюк с XML работает очень хорошо. Но если я выполню, как в вашем примере, он получит 0 строк для обновления. Если я удалю WHERE t1.value IS NULL, он обновит каждую строку. Так что, если я хочу обновить только id_data='43'? Кроме того, как я могу проверить ',' только на id_data='46', а не там, где он не равен нулю? (помимо 43 и 46, которые могут содержать данные, существует гораздо больше id_data). Я обновлю исходный вопрос тем, что пробовал. Большое спасибо за твою помощь. - person vvic; 16.06.2017
comment
Я уже пробовал это, но получал ошибку из-за какого-то странного нуля, теперь он исправлен и работает отлично. Большое спасибо. - person vvic; 16.06.2017