Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

combining fields from one select to update a single field in a separate statement.

i have a stored procedure trying to do the following:

SELECT field1, field2, field3...
FROM tableA
WHERE id = @id

Field1 = 1
Field2 = 0
Field3 = 1
Field4 = 0

If the field has a value of 1 Then I want to add that field # to my input into the next table

UPDATE TableB
SET combinedvalue = @values
WHERE id = @someid

in this case, @values should equal 13  (because both field1 and field3 have values of 1)

Thanks in advance.
0
kojak33
Asked:
kojak33
1 Solution
 
rafranciscoCommented:
Try this:

SELECT @Field1 = Field1, @Field2 = Field2, @Field3 = Field3...
FROM TableA
WHERE ID = @ID

UPDATE TableB
SET CombinedValue = ISNULL(CAST(NULLIF(Field1 * 1, 0) AS VARCHAR(2)), '') +
                                 ISNULL(CAST(NULLIF(Field2 * 2, 0) AS VARCHAR(2)), '') +
                                 ISNULL(CAST(NULLIF(Field3 * 3, 0) AS VARCHAR(2)), '') ...
WHERE ID = @SomeID
0
 
obahatCommented:
Good answer, however you can do this in a single statement. No need to store the variables, assuming the the IDs are common.

UPDATE b
SET combinedvalue = ISNULL(CAST(a.Field1*1 AS VARCHAR), '')
        + ISNULL(CAST(a.Field1*2 AS VARCHAR), '')
        + ISNULL(CAST(a.Field1*3 AS VARCHAR), '')
        + ...
FROM TableB b
        INNER JOIN TableA a
        ON a.ID = b.ID
WHERE a.ID = @CommonID

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now