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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy