Post reply

Update with select replace

  • February 28, 2012 at 12:46 pm

    #1452783

    Sean Lange (2/28/2012)

    rabisco (2/28/2012)

    What I'm trying accomplish is this.

    The ideal.

    For every paragraph , users should enter a value for code1 and code2.

    The current situation.

    Users have created some documents which have only values for code1 or code 2

    The proposed solution.

    1. A sql job to look through all the rows (bodytext column) of the documentbody table

    2. Find an occurence of a vale for code1,

    3. Look for the corresponding code2 value in the link table

    4. Update the code2 value where it is missing.

    Note that some rows have multiple paragrahs . Essentially, the same operation will be carried out on every paragraph where there is one of code1 or code2 missing.

    Any paragraphs or rows with both code1 or code2 missing, will not be processed.

    I hope this makes it clearer.

    Thanks again.

    Hey Sean, what was I provided enough for you to go on?

    Thanks.

    What I was really hoping you would do is to read the details in my post. Your samples are full of syntax errors. Since you are unable or not willing to make this easy for somebody to help I went ahead and formatted your sample data so it can be easily consumed.

    CREATE TABLE [dbo].[Linktable](

    [id] [bigint] IDENTITY(1,1) NOT NULL,

    [part_ref] [bigint] NOT NULL,

    [code1_sku] [varchar](255) NULL,

    [code2_sku] [varchar](255) NULL)

    go

    insert Linktable

    SELECT 11259,'1595501','83K3087' UNION ALL

    SELECT 11260,'1595474','83K3074' UNION ALL

    SELECT 11261,'1727495','66F9349' UNION ALL

    SELECT 11262,'1727584','66F9341' UNION ALL

    SELECT 11263,'1728200','66F9331' UNION ALL

    SELECT 11264,'1729014','66F9330' UNION ALL

    SELECT 11265,'1729156','66F9347' UNION ALL

    SELECT 11266,'1730135','66F9343' UNION ALL

    SELECT 11267,'1730185','66F9333' UNION ALL

    SELECT 11268,'1730523','66F9342' UNION ALL

    SELECT 11269,'1732360','66F9334' UNION ALL

    SELECT 11270,'1732364','66F9344' UNION ALL

    SELECT 11271,'1732413','66F9335' UNION ALL

    SELECT 11272,'1732423','66F9345' UNION ALL

    SELECT 11273,'1741637','02F6689' UNION ALL

    SELECT 11274,'1741646','02F6675' UNION ALL

    SELECT 11275,'1741660','02F6727' UNION ALL

    SELECT 11276,'1741667','02F6732' UNION ALL

    SELECT 11277,'1741673','02F6728' UNION ALL

    SELECT 11278,'1741675','02F6763'

    go

    CREATE TABLE [dbo].[documentBody](

    [bodyID] [bigint] NOT NULL,

    [bodyText] varchar(max) NULL)

    go

    insert documentBody

    SELECT 1000, null UNION ALL

    SELECT 1001, null UNION ALL

    SELECT 1002, null UNION ALL

    SELECT 1003, null UNION ALL

    SELECT 1004, null UNION ALL

    SELECT 1005, null UNION ALL

    SELECT 1006, null UNION ALL

    SELECT 1007, '<body>

    <a name="productLink" class="productLink" code1="1815000" href="">sku1</a></body>' UNION ALL

    SELECT 1008, '<body>

    <a name="productLink" class="productLink" code1="1833439" href="">sku2</a></body>' UNION ALL

    SELECT 1009, '<body>

    <a name="productLink" class="productLink code1="1833439" href="" uid="13298529604731501">sku3</a>

    <a name="productLink" class="productLink" code1="1743011" href="">sku4</a></body>' UNION ALL

    SELECT 1010, '<body>

    <a name="productLink" class="productLink code1="1498235" href="" uid="13298535612357259">sku5</a>

    <a name="productLink" class="productLink" code1="1841793" href="">sku6</a></body>'

    It is still totally unclear to me what you are trying to do. What would be the desired result based on this sample data?

    Ah - I see my mistake - apologies for the confusion

    Please replace the previous insert documentBody statement with the one below...

    SELECT 1000, null UNION ALL

    SELECT 1001, null UNION ALL

    SELECT 1002, null UNION ALL

    SELECT 1003, null UNION ALL

    SELECT 1004, null UNION ALL

    SELECT 1005, null UNION ALL

    SELECT 1006, null UNION ALL

    SELECT 1007, '<body>

    <a name="productLink" class="productLink" code1="1595501" href="">sku1</a></body>' UNION ALL

    SELECT 1008, '<body>

    <a name="productLink" class="productLink" code1="1595474" href="">sku2</a></body>' UNION ALL

    SELECT 1009, '<body>

    <a name="productLink" class="productLink code1="1727495" href="" uid="13298529604731501">sku3</a>

    <a name="productLink" class="productLink" code1="1727584" href="">sku4</a></body>' UNION ALL

    SELECT 1010, '<body>

    <a name="productLink" class="productLink code1="1728200" href="" uid="13298535612357259">sku5</a>

    <a name="productLink" class="productLink" code1="1729014" href="">sku6</a></body>'

    With the new insert, I'm hoping this should be clearer...

    The proposed solution.

    1. A sql job to look through all the rows (bodytext column) of the documentbody table

    2. Find an occurence of a value for code1,

    3. Look for the corresponding code2 value in the link table

    4. Update the code2 value where it is missing. (update with code2=xxxxxx)

    Note that some rows have multiple paragrahs . Essentially, the same operation will be carried out on every paragraph where there is one of code1 or code2 missing.

    Any paragraphs or rows with both code1 or code2 missing, will not be processed.

    Thanks.

  • March 1, 2012 at 5:57 am

    #1453569

    rabisco (2/28/2012)

    Sean Lange (2/28/2012)

    rabisco (2/28/2012)

    What I'm trying accomplish is this.

    The ideal.

    For every paragraph , users should enter a value for code1 and code2.

    The current situation.

    Users have created some documents which have only values for code1 or code 2

    The proposed solution.

    1. A sql job to look through all the rows (bodytext column) of the documentbody table

    2. Find an occurence of a vale for code1,

    3. Look for the corresponding code2 value in the link table

    4. Update the code2 value where it is missing.

    Note that some rows have multiple paragrahs . Essentially, the same operation will be carried out on every paragraph where there is one of code1 or code2 missing.

    Any paragraphs or rows with both code1 or code2 missing, will not be processed.

    I hope this makes it clearer.

    Thanks again.

    Hey Sean, what was I provided enough for you to go on?

    Thanks.

    What I was really hoping you would do is to read the details in my post. Your samples are full of syntax errors. Since you are unable or not willing to make this easy for somebody to help I went ahead and formatted your sample data so it can be easily consumed.

    CREATE TABLE [dbo].[Linktable](

    [id] [bigint] IDENTITY(1,1) NOT NULL,

    [part_ref] [bigint] NOT NULL,

    [code1_sku] [varchar](255) NULL,

    [code2_sku] [varchar](255) NULL)

    go

    insert Linktable

    SELECT 11259,'1595501','83K3087' UNION ALL

    SELECT 11260,'1595474','83K3074' UNION ALL

    SELECT 11261,'1727495','66F9349' UNION ALL

    SELECT 11262,'1727584','66F9341' UNION ALL

    SELECT 11263,'1728200','66F9331' UNION ALL

    SELECT 11264,'1729014','66F9330' UNION ALL

    SELECT 11265,'1729156','66F9347' UNION ALL

    SELECT 11266,'1730135','66F9343' UNION ALL

    SELECT 11267,'1730185','66F9333' UNION ALL

    SELECT 11268,'1730523','66F9342' UNION ALL

    SELECT 11269,'1732360','66F9334' UNION ALL

    SELECT 11270,'1732364','66F9344' UNION ALL

    SELECT 11271,'1732413','66F9335' UNION ALL

    SELECT 11272,'1732423','66F9345' UNION ALL

    SELECT 11273,'1741637','02F6689' UNION ALL

    SELECT 11274,'1741646','02F6675' UNION ALL

    SELECT 11275,'1741660','02F6727' UNION ALL

    SELECT 11276,'1741667','02F6732' UNION ALL

    SELECT 11277,'1741673','02F6728' UNION ALL

    SELECT 11278,'1741675','02F6763'

    go

    CREATE TABLE [dbo].[documentBody](

    [bodyID] [bigint] NOT NULL,

    [bodyText] varchar(max) NULL)

    go

    insert documentBody

    SELECT 1000, null UNION ALL

    SELECT 1001, null UNION ALL

    SELECT 1002, null UNION ALL

    SELECT 1003, null UNION ALL

    SELECT 1004, null UNION ALL

    SELECT 1005, null UNION ALL

    SELECT 1006, null UNION ALL

    SELECT 1007, '<body>

    <a name="productLink" class="productLink" code1="1815000" href="">sku1</a></body>' UNION ALL

    SELECT 1008, '<body>

    <a name="productLink" class="productLink" code1="1833439" href="">sku2</a></body>' UNION ALL

    SELECT 1009, '<body>

    <a name="productLink" class="productLink code1="1833439" href="" uid="13298529604731501">sku3</a>

    <a name="productLink" class="productLink" code1="1743011" href="">sku4</a></body>' UNION ALL

    SELECT 1010, '<body>

    <a name="productLink" class="productLink code1="1498235" href="" uid="13298535612357259">sku5</a>

    <a name="productLink" class="productLink" code1="1841793" href="">sku6</a></body>'

    It is still totally unclear to me what you are trying to do. What would be the desired result based on this sample data?

    Ah - I see my mistake - apologies for the confusion

    Please replace the previous insert documentBody statement with the one below...

    SELECT 1000, null UNION ALL

    SELECT 1001, null UNION ALL

    SELECT 1002, null UNION ALL

    SELECT 1003, null UNION ALL

    SELECT 1004, null UNION ALL

    SELECT 1005, null UNION ALL

    SELECT 1006, null UNION ALL

    SELECT 1007, '<body>

    <a name="productLink" class="productLink" code1="1595501" href="">sku1</a></body>' UNION ALL

    SELECT 1008, '<body>

    <a name="productLink" class="productLink" code1="1595474" href="">sku2</a></body>' UNION ALL

    SELECT 1009, '<body>

    <a name="productLink" class="productLink code1="1727495" href="" uid="13298529604731501">sku3</a>

    <a name="productLink" class="productLink" code1="1727584" href="">sku4</a></body>' UNION ALL

    SELECT 1010, '<body>

    <a name="productLink" class="productLink code1="1728200" href="" uid="13298535612357259">sku5</a>

    <a name="productLink" class="productLink" code1="1729014" href="">sku6</a></body>'

    With the new insert, I'm hoping this should be clearer...

    The proposed solution.

    1. A sql job to look through all the rows (bodytext column) of the documentbody table

    2. Find an occurence of a value for code1,

    3. Look for the corresponding code2 value in the link table

    4. Update the code2 value where it is missing. (update with code2=xxxxxx)

    Note that some rows have multiple paragrahs . Essentially, the same operation will be carried out on every paragraph where there is one of code1 or code2 missing.

    Any paragraphs or rows with both code1 or code2 missing, will not be processed.

    Thanks.

    Hi Sean, apologies for not being clear about what was trying to achieve in my earlier posts. Has the change to the documentbody insert statement helped to make it clearer?

    Thanks.

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply