SQL Server query problem in updating the table

Question

I have two tables Table1 and Table2.

I want to update Table1 with data from Table2 with this following format. In comma-separated value when JobId is the same:

Table1ColumnName Update = (Quantity * Length  [TYPE]  ---Table2 Column)

Table1

Table2

Expected result would be very similar to this:

I strongly suggest against this. Storing delimited data is a bad idea in an RDBMS and makes it much harder to query when you do have delimited information. Keep the data in the format you have, with separate columns and rows which each distinct piece of information. — Thom A, Jul 09 '19 at 10:17
I also recommend that `Length` should be a numerical data type, not a `varchar` with the word `'feet'`. Especially as you're inconsistently storing the values and ordering will be wrong; for example `'20feet'` <> `'20 feet'` and `'40 feet'` > `'100 feet'` (assuming you'll have other lengths). If you do need to store the measurement type (i.e. feet, inches, metres, etc), then use a separate column to store it. — Thom A, Jul 09 '19 at 10:21
Further to the recommendations on the `Length` column. You can't do what you want (`Quantity * Length`) because Length is not a number — Nick.Mc, Jul 09 '19 at 10:52
i got my answer.....https://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value — THE LIFE-TIME LEARNER, Jul 09 '19 at 10:54
Possible duplicate of [Multiple rows to one comma-separated value](https://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value) — Amira Bedhiafi, Jul 09 '19 at 11:08
to add to @Larnu 's comments you should also ask yourself the question if you really want to store precalucated information/totals in general and maintain possibly the values in synchronisation with some triggers.. You are better of generating the CSV list on the fly when you select.. — Raymond Nijland, Jul 09 '19 at 11:30
You've found your answer but if you ignore the advice on here you're building a substandard system. — Nick.Mc, Jul 10 '19 at 08:59

mkRabbani · Answer 1 · 2019-07-09T11:05:06.770

0

Try this below code. Please keep in mind that UPDATE is a risky operation and try the code with your test data first.

UPDATE A
SET A.CD_ContrType = CAST(@Quantity AS VARCHAR(MAX)) + ' * ' + Length + ' * ' + TYPE
FROM Table1 A
INNER JOIN Table2 B ON A.MainJob_id = B.MainJob_id

edited Jul 09 '19 at 11:05

answered Jul 09 '19 at 10:56

mkRabbani

16,295
2
15
24

score 0 · Answer 2 · answered Jul 09 '19 at 11:01

0

Try this query:

update t1 set CD_ContrType = cast(t2.Quantity as varchar(10)) + '*' + t2.Length
from table1 t1
join table2 t2 on t1.MainJob_Id = t2.MainJob_Id

answered Jul 09 '19 at 11:01

Michał Turczyn

32,028
14
47
69

score 0 · Answer 3 · answered Jul 09 '19 at 11:08

This is probably a bad idea. Usually, storing comma delimited values is not a good approach. However, if you are just doing this for presentation purposes, it might not be such a bad idea.

In SQL Server, you can use FOR XML PATH for the string concatenation:

update t1
    set cd_contrtype = t2.new_cd_contrtype
    from table1 t1 join
         (select t2.main_job_id,
                 stuff( (select concat(', ', tt2.quantity, ' * ', tt2.length, ' ', tt2.type)
                         from table2 tt2
                         where tt2.main_job_id = t2.main_job_id
                         for xml path ('')
                        ), 1, 2, ''
                      ) as new_cd_contrtype
          from (select distinct main_job_id from table2 t2) t2
          group by t2.main_job_id
         ) 
         on t2.main_job_id = t1.main_job_id

Brijesh Kumar Tripathi · Accepted Answer · 2019-07-09T14:32:01.367

Please check this query however I suggest to rethink about your approach because storing comma separated value is not a good idea:

Creation of sample data

Declare @Table1 table(
CD_Contr_Type varchar(250), 
MainJob_Id varchar(100)
)
Insert into @Table1 values (NULL, '201900002')
Insert into @Table1 values (NULL, '201900004')
Insert into @Table1 values (NULL, '201900006')
Insert into @Table1 values (NULL, '201900008')
Insert into @Table1 values (NULL, '201900010')
Insert into @Table1 values (NULL, '201900012')
Insert into @Table1 values (NULL, '201900013')
Insert into @Table1 values (NULL, '201900015')

Declare @Table2 table(
MainJob_Id varchar(100),
Quantity decimal(5,3),
Length varchar(20),
Type varchar(100)
)
Insert into @Table2 values('201900002', 1.000, '20Feet', 'Flat Rate')
Insert into @Table2 values('201900004', 1.000, '20Feet', 'Bulk')
Insert into @Table2 values('201900004', 2.000, '40Feet', 'Bulk')
Insert into @Table2 values('201900006', 1.000, '20Feet', 'General Purpose')
Insert into @Table2 values('201900008', 1.000, '20Feet', 'Bulk')
Insert into @Table2 values('201900010', 1.000, '20Feet', 'Bulk')
Insert into @Table2 values('201900012', 1.000, '20Feet', 'High Cube')
Insert into @Table2 values('201900013', 1.000, NULL, 'Loose Cargo')
Insert into @Table2 values('201900015', 1.000, '20Feet', 'General Purpose')

Final Update Query

Update A set CD_Contr_Type = UpdateVal from @Table1 A inner join (
Select MainJob_Id, 
(Select Cast(Cast(Quantity as int) as varchar) + ' * ' + Length + ' ' + Type + ', ' 
from @Table2 t2 
where t2.MainJob_Id = t1.MainJob_Id for XML Path('')) as UpdateVal 
from @Table1 t1 
group by MainJob_Id) B on A.MainJob_Id = B.MainJob_Id

SQL Server query problem in updating the table

4 Answers4