Does UPDATE syntax require listing ALL fields from TableA & TableB ??

[mb224]

Suppose I want to update TableA (50 rows) with TableB (single row) .. where the COLUMN NAMES (about 80 columns) are the same for both tables .....

is there a way to avoid having to list all the columns I want to update .... say I want to update 60 "contiguous" columns in TableA ... do I have to SET all 60 columns ..or is there a way to kind of match the colums in the 2 tables

UPDATE TableA
SET col3 = b.col3,
col4 = b.col4,
col5 = b.col5,
col6 = b.col6,
col7 = b.col7
FROM TableA a , TableB b

???????????? I'm I being lazy? .... please help!

 

[JayKusch]

Nope .... you are going to have to list the intended fields you wish to update. If you are "lazy" or I like to think of it as creative ... script out the table to a text file. Open the file w/ a text editor that permits you to copy a "block" of text and paste it directly in line w/ the block that was cut so that the fields line up. Then do a few global updates to make each side of the update statement syntactically correct and boom ... your done and ready to paste it into Query Analyzer and execute it.

BTW ... TextPad is a great text editing app to accomplish this. I am sure there are many more but you know how it is once you find a tool that serves a purpose, you rarely go out anf find a new one - lol

Thanks

J. Kusch

 

[mb224]

thanks

 

[SQLSister]

Personally I drag the fields over from the object browser in query analyzer. If you open the table object and then the columns, you can drag individual columns or drag all in one step by dragging the word columns over (but you have to see the column list for this to work).

At least when you do this, then you don't have any typing errors and it saves a lot of time when doing these long update statments.

Questions about posting. See faq183-874