0

I am using a development environment with Lucee and SQL Server Management Studio. I have two tables:

  • Cities - Columns: city, state, region, and zip.
  • Info - Columns: city, state, zip

There are 80,000+ entries Info. I ONLY want to update the zip column in the Cities table. It is all null for each of the 22,000 entries. If there is a matching value for City and State, assign the value to Cities based off Info. I haven't figured out how to go through the whole query like that without having some kind of search value.

I've tried things like the code below. It outputs 10 from Cities, and the last index [9] from Info in 2 dumps. Why does it not display each iteration of i? It could be 30,000 in order to go through the whole query but still how do I update based on a value in Info and do it ONLY ONCE even if there are multiple matching values in Info. Also, if matching values do not exist in Info, how to not set any incorrect information?

<cfquery name="list" datasource="source">
    SELECT TOP 10 *
    FROM cities
    ORDER BY CITY ASC
</cfquery>

<cfparam name="i" default="1">
<cfloop query="#list#" from="1" to="10" index="i">
  <cfoutput>#list.city[i]#</cfoutput>
  <cfoutput>#list.state[i]#</cfoutput>
</cfloop>
<cfquery name="master" datasource="source">
    SELECT TOP 10 *
    FROM WEBMASTERCOPY
    WHERE STATE = '#list.state[i]#' AND city = '#list.city[i]#'
</cfquery>
Community
  • 1
  • 1
needingSomeAnswers
  • 13
  • 5
  • i read your information and write this query for you update cities set cities.zip = info.zip from info where info.city=cities.city and info.sate=cities.state and info.zip = null – Ehsan Panahi Jul 31 '18 at 14:06
  • Please [edit](https://stackoverflow.com/posts/51615103/edit) your question and add the formatted SQL query (so it's easier to read). The syntax isn't quite right. Look at the last query in the link above, it shows how the MySQL JOIN needs to be in the UPDATE (there's no FROM clause). Something more like this `UPDATE cities c INNER JOIN info i ON i.state = c.state AND i.city = c.city SET c.zip = i.zip WHERE c.zip IS NULL` – SOS Jul 31 '18 at 14:46
  • @EhsanPanahi Your SQL statement was exactly what I needed!!! At first 0 lines where update and I took away The c.zip is NULL and it worked perfectly! Thank you!!!! – needingSomeAnswers Jul 31 '18 at 15:25
  • Possible duplicate of [How to do 3 table JOIN in UPDATE query?](https://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query) – SOS Jul 31 '18 at 16:04
  • @EhsanPanahi - That's SQL Server syntax. MySQL uses a different syntax https://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query – SOS Jul 31 '18 at 19:11
  • Possible duplicate of https://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server – SOS Aug 09 '18 at 11:57

2 Answers2

0

It ended up being like this:

 update Cities
   set Cities.ZIP = Info.ZIP from Info 
   where Cities.STATE = Info.STATE
 and 
Cities.CITY = Info.CITY
needingSomeAnswers
  • 13
  • 5
  • If that syntax works - you're not using MySQL! It's valid for SQL Server, etc.. though. – SOS Jul 31 '18 at 16:03
  • @Ageax You're right, it's Microsoft SQL Server Management Studio – needingSomeAnswers Aug 01 '18 at 18:31
  • That makes a big difference ;-) SQL syntax is often vendor specific, so be sure to tag future questions with the correct dbms to get an accurate answer. – SOS Aug 07 '18 at 15:28
-1

You shouldn't need ColdFusion, though you can use cfquery. This is MySQL that works regardless:

UPDATE cities t1, WEBMASTERCOPY t2
SET t1.zip = t2.zip
WHERE t1.city = t2.city AND t1.state = t2.state AND t1.zip IS NULL

Make a backup first =)

Jules
  • 1,941
  • 15
  • 18