update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update query

Author

Topic

vswvsw
Starting Member

13 Posts

Posted - 2011-07-06 : 04:16:45

hi,

i am new to sql.
is there any query which help me to update a coloum like the below mentioned instead using joins.
update table1
set table1.col1=table2.col1
where table1.col2=table2.col2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-06 : 04:44:36

It is the right way using joins.
Don't you like joins or what is your problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

vswvsw
Starting Member

13 Posts

Posted - 2011-07-06 : 08:06:21

thank you for your reply
I have two tables
table 1
r1 r2 r3 r4
x 1 50
x 1 60
z 3 70

table 2
r11 r12 r13
x acv 50

i need an insert statement that will insert asv in r4 in table 1 based on table2.r11=table1.r1.
Can you please....Once more I am very new to sql

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-06 : 08:59:10

update t1
set r4 = t2.r12
from table1 as t1
join table2 as t2 on t1.r1 = t2.r11

No, you're never too old to Yak'n'Roll if you're too young to die.

vswvsw
Starting Member

13 Posts

Posted - 2011-07-06 : 09:14:53

THANKYOU FOR YOUR REPLY..WORKS GOOD..
BUT ME NT INTERESTED IN JOINS....

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-07-06 : 09:27:24

You may be interested in this;

http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-06 : 10:41:24

I love this syntax and have no reason to do it in another way

No, you're never too old to Yak'n'Roll if you're too young to die.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-07-06 : 13:35:39

I was
1. showing the OP the inefficient ANSI syntax.
2. trying to get the OP to understand the pitfalls of a one to many join with the UPDATE FROM syntax. Occasionally I have found MERGE a bit too keen to do a table scan. One of the suggestions in the comments was to ask Microsoft to alter the UPDATE FROM so that it produces the same error as MERGE if a one to many relationship exists.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources