Update inner join - 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 inner join

Author

Topic

pianoboy11
Starting Member

5 Posts

Posted - 2010-03-08 : 18:17:32

Hi guys I really need help basically im trying to do a inner join update from table2 to table1. How to do the sql statement for this

Example

table1

ID Fruit Color ETag Value

1 apple red TAG_TEST 4

2 orange orange TAG_TEST 3

3 cat white TAG_TEST 2

1 apple red TAG_TEST 1

1 apple red TAG_TEST 2

table2

Value Etag

0 BAD

1 Good

2 pretty

3 ok

4 beautiful

table2 has the etag description of the value.
so 0 means bad, 1 means good, 2 means pretty etc.......

Im trying to pull the actual description from table2 and update the etag column where table1.Color -'red'

So basically im searching for the color red and the value red.
then I look the value from table 2 to get the Etag description.
once you get the description depending on the value update table1.Etag
with the description coming from table2

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2010-03-08 : 18:46:26

Pianoboy11,

There are many ways to do this & try the following & this should work.

regards,
Anil

update table1
set table1.etag = (select b.etag from table2 b where b.value = table1.value)

pianoboy11
Starting Member

5 Posts

Posted - 2010-03-08 : 18:47:37

for some reason the form push my columns together, you have to spread the rows and columns out to understand

pianoboy11
Starting Member

5 Posts

Posted - 2010-03-08 : 18:58:00

he result table should be like this which is table1

ID Fruit Color ETag Value

1 apple red beautiful 4

2 orange orange TAG_TEST 3

3 cat white TAG_TEST 2

1 apple red Good 1

1 apple red pretty 2

pianoboy11
Starting Member

5 Posts

Posted - 2010-03-08 : 19:02:25

what is b reference to in your query

pianoboy11
Starting Member

5 Posts

Posted - 2010-03-08 : 19:13:42

its not working, how does your quesy search for red

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2010-03-09 : 03:51:14

Im not sure what you are asking here. b is an alias for table2. Have you tried running my query ?

It just works fine with what I wrote in here & will certainly update your etag in table1 to that of the value of etag in table2.

Try that & let us know.

regards,
Anil Kumar

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 05:03:33

As simple as this maybe?


UPDATE t1 SET
	[ETag] = t2.[Etag]
FROM
	table1 t1
	JOIN table2 t2 ON t2.[value] = t1.[value]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources