June 12, 2008 at 9:34 pm
I have a table in the following format. All the records will be in the same format with these four rows repeating with different val1 values (like 100, 98...)
ID Color Val Val1
-------------------------------
1 K 1.5 100
2 C 1.6 100
3 M 1.7 100
4 Y 1.8 100
I have done a self join to get the data in the following format
ID Color Val ID Color Val ID Color Val ID Color Val
------------------------------------------------------
1 K 1.5 2 C 1.6 3 M 1.7 4 Y 1.8
The query used is,
select a.id, a.color, a.val, b.id, b.color, b.val, c.id, c.color, c.val, d.id, d.color, d.val from test a inner join test b on a.val1 = b.val1 inner join test c on b.val1 = c.val1 inner join test d on d.val1 = c.val1 where a.color = 'K' and b.color = 'C' and c.color = 'M' and d.color = 'Y'
My update statement looks like the one below.
UPDATE test
SET val = CASE WHEN color = 'K' THEN 2.1
WHEN color = 'C' THEN 2.2
WHEN color = 'M' THEN 2.3
WHEN color = 'Y' THEN 2.4
END
WHERE val1 = 100
Similar to the update statement I want to write an Insert statement for the result I retrieve. I am using a dataadapter to bind the records to a datagridview which shows up the records in the front end. So in order to update and insert new records using the dataadapter I need to provide the updatecommand and insertcommand. The above given updatecommand works fine. I just want to know if the insert is also possible.
June 13, 2008 at 4:59 am
June 13, 2008 at 8:15 am
Read up on the insert syntax from BOL
INSERT INTO destination
(field1, field2 ....)
Select field1, field2
from table1
Pretty straightforward
~PD
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply