Can the following dbo.DelimitedSplit8k be done in one query?

• isuckatsql

  SSChampion

  Points: 10381

  More actions

  February 10, 2012 at 11:35 am

  #391531

  update Profiles

  set Profiles.Industry1id = (SELECT i.Id FROM AllJobCategories j CROSS APPLY dbo.DelimitedSplit8k(jobcategories1,'(') split

  inner join Industries i on Item = i.Name where j.UniqueId = 'DF036598-E190-422A-863B-000036B64FFE' and itemnumber = 1)

  where Profiles.Id = 'DF036598-E190-422A-863B-000036B64FFE'

  The above query works fine.

  set rowcount 20;

  update Profiles

  set Profiles.Industry1id = (SELECT top 1 i.Id FROM AllJobCategories j CROSS APPLY dbo.DelimitedSplit8k(jobcategories1,'(') split

  inner join Industries i on Item = i.Name

  inner join Profiles on j.Uniqueid = Profiles.id

  where itemnumber = 1)

  When i remove the hard coded id, it works if i use 'Top 1' but always gives the same result, and if i remove 'Top 1' i get an error saying subquery has more than one value!

  I am tryng to match the industry names, then update the Profile Table with the numerical value associated with that industry name.

  Thanks

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 10, 2012 at 11:44 am

  #1445716

  = (SELECT must return one value per row;

  here's what i think is the equivalent , but since the subquery would return more than one industry id per profile, you don't know which value it gets update dto.

  update subquery version(formatted)

  UPDATE Profiles

  SET Profiles.Industry1id = (SELECT

  TOP 1

  i.Id

  FROM AllJobCategories j

  CROSS APPLY dbo.DELIMITEDSPLIT8K(jobcategories1, '(') split

  INNER JOIN Industries i

  ON Item = i.Name

  INNER JOIN Profiles

  ON j.Uniqueid = Profiles.id

  WHERE itemnumber = 1)

  UPDATE FROM version:

  UPDATE Profiles

  SET Profiles.Industry1id = i.Id

  FROM AllJobCategories j

  CROSS APPLY dbo.DELIMITEDSPLIT8K(jobcategories1, '(') split

  INNER JOIN Industries i

  ON Item = i.Name

  WHERE j.Uniqueid = Profiles.id

  AND itemnumber = 1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 10, 2012 at 11:58 am

  #1445718

  here's the equivalent SELECT so you can see what would get updated...does it look correct?

  SELECT * FROM Profiles

  INNER JOIN AllJobCategories j

  ON j.Uniqueid = Profiles.id

  CROSS APPLY dbo.DELIMITEDSPLIT8K(jobcategories1, '(') split

  INNER JOIN Industries i

  ON Item = i.Name

  WHERE itemnumber = 1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• isuckatsql

  SSChampion

  Points: 10381

  More actions

  February 10, 2012 at 12:42 pm

  #1445749

  Seems to be working so far 🙂

  Many Thanks!

  Cute corgi ...... great dogs!