update query with joins

After a long break,

updating column using multiple table joins .

simple trick to remember

first select that column value and ensure we are getting correct value

e.g.

SELECT  NAME

FROM

COUNTRY INNER JOIN CONTINENTS C ON COUNTRY. CONTINENT_ID = C.ID

WHERE COUNTRY.TYPE =’ISLAND’

now If we want to update selected column to other table then just replace select statement with Update statement

UPDATE

    NEW_TABLE

SET

   ISLAND_COUNTRY = COUNTRY.NAME

FROM

   COUNTRY INNER JOIN CONTINENTS C ON COUNTRY. CONTINENT_ID = C.ID

  WHERE COUNTRY.TYPE =’ISLAND’

and done.

 

If you observe ,only select statement is replaced and all good.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s