June 2, 2008 at 12:52 am
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
-------------------------------
1K1.5100
2C1.6100
3M1.7100
4Y1.8100
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'
Is it possible to write a single update statement to perform update on self - joined table?
Basically the val column will be updated for the various colors.
Any help will be appreciated.
June 2, 2008 at 1:48 am
If you want to write a single update statement for the original table that you will later query witht he stetement you mention, you can use somehting like:
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
Not sure why you would like to have a single update statememt though. You could use transactions with four update statements, or you could jsut write a stored procedure to do this.,
Regards,
Andras
June 2, 2008 at 3:27 am
Thanks for the reply. Basically this is the format I will be displaying in the front end. I will be binding this data directly to the datagridview using a dataadapter. Now to update the changes made in the grid, I need to provide a single update statement. The code you have given above updates the val field. If there is another field like val which has to updated then what will the statement look like.
Thanks.
June 5, 2008 at 12:45 pm
I don't know if this is what you were looking for but to add another column to the update statement, you will do something like :
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,
val2 = 'test'
WHERE val1 = 100
June 9, 2008 at 4:43 am
If in this case I want to write an INSERT statement then how do I do that?
July 22, 2008 at 7:27 am
Its easier to select the data that needs to be inserted first, so in your example it can be something like:
insert into test(val,val2)
( select
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 val,
val2
from test
WHERE val1 = 100 )
July 27, 2008 at 2:27 am
Hope my article will get you to achieve the same,
http://venkattechnicalblog.blogspot.com/2008/07/rows-to-columns-in-sql-server.html
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply