Update query's where clause values from a datatable

Question

I have this query:

UPDATE A
Set A.NUM = B.NUM
FROM A
JOIN B  on A.ID = B.ID
where A.Code in ()

A.Code values are from a datatable. How do I feed into this query?

The problem is in the parenthesis. I need to feed values from a datatable. Something like (select column1 from datatable) — Meidi, Jun 24 '14 at 18:20
@juergend That's old now. Table-valued parameters are the new hotness, _especially_ if you already have a datatable. — Joel Coehoorn, Jun 24 '14 at 18:23
It is a .NET DataTable. Be specific with the correct capitalization. Is it just he value for the first row or all rows. — paparazzo, Jun 24 '14 at 18:36

Ashish Charan · Answer 1 · 2014-06-24T19:43:44.037

2

Why not write a code to make a comma separated string of ID's using datatable?

            string lstOfIDs = string.Empty;
            DataTable dt = new DataTable();
            foreach (DataRow drow in dt.Rows)
            {
                lstOfIDs += drow["IdColumnHere"].ToString()+",";
            }

            lstOfIDs.TrimEnd(',');

You can then pass the lstOfIds in the IN clause.

EDIT 1:

I think A.Code In () is checking for code not Ids. I hope you are placing codes in the lstOfIDs. Also, I would advise putting ' between Id's. i.e.

lstOfIDs += "'"+drow["IdColumnHere"].ToString()+"',";

this should give you something like 'abc','def','anything'

edited Jun 24 '14 at 19:43

answered Jun 24 '14 at 18:27

Ashish Charan

2,347
4
21
33

This method is not working for me. Too many IDs in the parenthesis caused the string would be truncated error. Also, the way to add comma is causing syntax error. it should be added in front of the value. – Meidi Jun 24 '14 at 19:37
Please check the edit. In case that doesn't work share your code. – Ashish Charan Jun 24 '14 at 19:44

score 1 · Answer 2 · answered Jun 24 '14 at 18:22

1

You want a Table-Valued Parameter.

This article will also help:

http://www.brentozar.com/archive/2014/02/using-sql-servers-table-valued-parameters/

If you have more columns than just Code in the C# Datatable, you may also need a projection (inside the sql) get output that will work with the IN() clause. Something like this:

UPDATE A
Set A.NUM = B.NUM
FROM A
JOIN B  on A.ID = B.ID
where A.Code in ( SELECT Code FROM @tvpCodes )

answered Jun 24 '14 at 18:22

Joel Coehoorn

399,467
113
570
794

I defined the Type and it kept complaining the type does not exist: so I add this line "Create TYPE dbo.MyTableType as TABLE( Code int);" in front of the query I had. and then use standard sql command to update it doesn't work. – Meidi Jun 24 '14 at 19:14

Update query's where clause values from a datatable

2 Answers2