copy subset of records? 2

[mlowe9]

current table:

key formname userid fieldname x_coor y_coor
--- -------- ------ --------- ------ ------
1   form1    matt   field1    1      1
2   form1    matt   field2    100    1
3   form1    matt   field3    200    1
4   form1    fred   field1    5      50
5   form1    fred   field2    500    50
6   form1    fred   field3    500    75
...many other records

what I want to get to:

key formname userid fieldname x_coor y_coor
--- -------- ------ --------- ------ ------
1   form1    matt   field1    1      1
2   form1    matt   field2    100    1
3   form1    matt   field3    200    1
4   form1    fred   field1    1      1
5   form1    fred   field2    100    1
6   form1    fred   field3    200    1

Can I accomplish this with a sql query?
Thanks
Matt

 

[hmckillop]

You can use

SELECT key formname userid fieldname x_coor y_coor
INTO MyNewTable
FROM MyCurrentTable
WHERE <where condition which provides your subset?

[code]

This will generate your new table structure and insert the data.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[markros]

select T.Key, T.FormName, T.UserID, T.FieldName, D.x_Coor, D.y_coor from myTable T inner join (select Key, FormName, UserID, FieldName, x_Coor, y_coor from myTable where UserID = 'matt') D on T.FormName = D.FormName and T.FieldName = D.FieldName

 

[mlowe9]

Thanks, but I messed up a little. I just want to update the original table, but change the records for "fred" to match what's in the table for "matt".

I wasn't specific enough. I apologize

 

[hmckillop]

Try

Update tbl1
SET tbl1.x_coor = tbl2.x_coor ,
tbl1.y_coor tbl2.y_coor
FROM MyCurrentTable tbl1
INNER JOIN 
(SELECT * from MyCurrentTable tbl3 where tbl3.userid = 'Matt') tbl2 on tb1.fieldname = tbl2.fieldname 
where tbl1.userid= 'fred'

Not 100% sure on this, didnt have time to put data in.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[mlowe9]

Thanks a million. Wasn't exactly what I needed, but definitely gave me the structure I needed with that embedded select statement.

 

[markros]

I don't know if you saw the SQL I posted - should have been the one you looked for.

 

[mlowe9]

I did see it, but did not notice that you did the embedded sql with the join also. I saw the select and assumed you were giving me something I wasn't wanting. So I will take the blame on that one and give you a star as well.

Sorry about that. If I'd looked at yours more deeply I probably could've used it as well.