Update Table 1 with Table 2. 2

[Extension]

Hi,

I would like know how to update records in a table with data from another table. Is it possible to do a join or to make a sub-query ?
With the data below (example), I would to update the City field in the UserTable with the City from the LocationTable. Both table have a relationship; CITY_CODE

Now, if you look at my query below, you'll that it's wrong, but it's logically what I want to achieve.

__UserTable__
ID  ! NAME         ! CITY      ! CITY_CODE !
243 ! Jeremy Lingo ! Seattllez ! S034      !
655 ! Bobby Bouche ! Portland  ! P001      !

__LocationTable__
ID   ! CITY      ! CITY_CODE !
9920 ! Portland  ! P001      !
2332 ! Seattle   ! S034      !
2299 ! New York  ! N202      !

UPDATE UserTable UsrTbl
SET CITY = (	select CITY 
		from LocationTable LocTbl 
		where UsrTbl.CITY_CODE = LocTbl.CITY_CODE
                and UsrTbl.CITYCODE = 'S034'
	      )

Thanks in advance.

 

[SantaMufasa]

Extension,

Your intuition is excellent. If you would have spelled "CITY_CODE" correctly on your last line of code, then it would have worked flawlessly.

Let us know if you have any other issues.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

Possibly the condition "UsrTbl.CITY_CODE = 'S034'" should be on the main query rather than the subquery.

Also, you have to be a little careful with these types of updates. The problem is when you are updating a table where only some of the rows have a match in the subquery e.g. if UserTable already has some data in CITY and some of the rows have no match in LocationTable. If there is no match, the rows will have CITY overwritten with NULL. This may not be a desirable result. To avoid this problem, use:

UPDATE UserTable UsrTbl
SET CITY = (    select CITY 
        from LocationTable LocTbl 
        where UsrTbl.CITY_CODE = LocTbl.CITY_CODE)
where UsrTbl.CITY_CODE = 'S034'
AND EXISTS
(    select 1
        from LocationTable LocTbl 
        where UsrTbl.CITY_CODE = LocTbl.CITY_CODE)

 

[Extension]

Thanks to both of you.

I was also wondering how to update two fields without having two subqueries. Something similar to this, but functional:

UPDATE UserTable UsrTbl
SET [b]CITY, CITY_2[/b] = (    select [b]CITY, CITY_2 [/b]
        from LocationTable LocTbl 
        where UsrTbl.CITY_CODE = LocTbl.CITY_CODE
                and UsrTbl.CITYCODE = 'S034'
          )

 

[Dagon]

Very close:

UPDATE UserTable UsrTbl
SET (CITY, CITY_2) = (    select CITY, CITY_2 
        from LocationTable LocTbl 
        where UsrTbl.CITY_CODE = LocTbl.CITY_CODE
                and UsrTbl.CITYCODE = 'S034'
          )

But I really recommend you get into the habit of putting a where exists. It will save you a lot of grief in the future when you accidently wipe the column in every row of the table.

 

[SantaMufasa]

Again, Extension, your intuition is very good. Your code, above, would work if you:

1) Change the spelling of "CITYCODE" to "CITY_CODE" as I mentioned in my first post, and

2) Place parentheses around your change-columns list.

So, (if there were a "CITY_2" column in both tables) your code would read:

UPDATE UserTable UsrTbl
set (city, city_2) =
(select city, city_2 from locationtable loctbl
  where usrtbl.city_code = loctbl.city_code
    and UsrTbl.city_code = 'S034');

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Extension]

Dagon

Thanks for your help. Really appreciated.
I've noticed while testing my query that the EXISTS condition is essential. I ended up with some NULL values because some CITY_CODEs didn't exist in the LocationTable.

I've added a new field (ADDRESS_CODE). You'll see that the relationship is simple between the two tables.

Now I want to use the EXISTS function, but I assume I would have to do a sub-query of a sub-query to achieve it with my curren query.

New data and Query

UPDATE UserTable UsrTbl
SET (CITY, CITY_2) = (
		SELECT CITY, CITY_2
		FROM LocationTable LocTbl
		WHERE LocTbl.CITY_CODE = 'S034'
		AND UsrTbl.ADDRESS_CODE = LocTbl.ADDRESS_CODE )

__UserTable__
ID  | NAME         | CITY      | ADDRESS_CODE |
243 | Jeremy Lingo | Seattle | S555      |
655 | Bobby Bouche | Seattle  | S555     |

__LocationTable__
ID   | CITY      | CITY_CODE | ADDRESS_CODE |
9920 | Portland  | P001      | P232 |
2332 | Seattle   | S034      | S555 |
2299 | New York  | N202      | N655 |

 

[taupirho]

Have you considered an updateable join view. I didn't know about these until recently. You can't use them all the time as there are some restrictions on their use but when you can - do it. Generally faster than a 'regular' update and to my mind much more readable. As I say I don't know if you will be able to use it but here's how you would.

update
(
select t1.city old_city, t1.city2 old_city2,t2.city new_city,t2.city2 new_city2
from UserTable t1, LocationTable t2
where t2.city_code = 'S034'
and t2.address_code = t1.address_code
)
set old_city = new_city,
old_city2 = new_city2


Read more here:

http://people.aapt.net.au/roxsco/tuning/updateable view.html

 

[Dagon]

Extension, I'm not quite sure what you mean. If you are just joining on Address_code instead of city_code, it would just look the same as before:

UPDATE UserTable UsrTbl
SET (CITY, CITY_2) = (
        SELECT CITY, CITY_2
        FROM LocationTable LocTbl
        WHERE LocTbl.CITY_CODE = 'S034'
        AND UsrTbl.ADDRESS_CODE = LocTbl.ADDRESS_CODE )
where exists
(SELECT 1
        FROM LocationTable LocTbl
        WHERE LocTbl.CITY_CODE = 'S034'
        AND UsrTbl.ADDRESS_CODE = LocTbl.ADDRESS_CODE )

You don't need to select any columns in the "where exists". That's why I just put "select 1" instead of "select columns".

 

[Extension]

taupirho:
Thank you for the information. I'll read more about it.

Dagon:
Thanks again for your help. My question was not really clear but you answered it. Thanks.