0

I have a perplexing problem that I am finding difficult to work through.

I have 2 tables in my MYSQL database containing similar data. I'll outline just the pertinent information.

Table1 - ezpay_Usage Columns: ticket_ID date_opened date_closed - No data in this column yet

Table2 - Closed_Tickets Columns: ticket_ID date_opened date_closed - Has all the data

Table2 has data in the column "date_closed". We just changed out DB Structure and added the same column to Table1 (Currently has no data in that column though). Both columns will reference the same data and going forward will be updated in unison.

My questions is, how can I update all the existing records in Table1 with the data for the date_closed column by comparing it to the data in Table2.

I was thinking perhaps this way?:

First, Query The Table with the Data in question

<cfquery name="source_table" datasource="#datasource#">
    select * 
    from closed_tickets 
</cfquery>'

Second, query the table I want to update

<cfquery name="tableToBeModified" datasource="#datasource#">
    select *
    from ezpay_usage
</cfquery>

Then, run an update query on the table I want to update

<cfquery name="update" query="#datasource#">
    update ezpay_usage
    set date_closed = #source_table.closed_date#
    where ticket_id = #source_table.tciket_id#
</cfquery>

Anyone have any suggestions for me? Thanks.

Brian Fleishman
  • 1,237
  • 3
  • 21
  • 43
  • 1
    Sounds like you just need a JOIN on the unique key ie `ticket_id`. This is a very common question: see [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) and [SQLFiddle](http://sqlfiddle.com/#!3/f66ff/1). Voting to close as a duplicate. BTW, sql syntax is often db dependent. To save time, please be sure to mention your dbms with any sql questions. – Leigh Sep 19 '14 at 14:36
  • Added my my DB type to the questions: MYSQL – Brian Fleishman Sep 19 '14 at 14:48
  • Does you example still apply to a MYSQL db? Thanks. – Brian Fleishman Sep 19 '14 at 14:55
  • Do a search on [`[mysql]` update join](http://stackoverflow.com/search?q=[mysql]+update+JOIN). You will see the general concept is the same. Only the position of the JOIN statement is slightly different. JOINs are pretty fundamental SQL, so you may want to review the docs as well [MySQL Docs - JOINS](https://dev.mysql.com/doc/refman/5.0/en/join.html). I would recommend create a few test tables in your db. Try it out. That is a good way to get comfortable with it. – Leigh Sep 19 '14 at 15:04
  • Could a ColdFusion WHERE statement achieve the same result? Similar to my example in my questions? – Brian Fleishman Sep 19 '14 at 15:16
  • You would need to run that last cfquery within a loop. ie CFLoop through the source_table query. (Use cfqueryparam on the values of course). That said, I am not sure why you would want to .. when you can do the same thing with a single `UPDATE`. – Leigh Sep 19 '14 at 15:20
  • I'm just finding the JOIN syntax confusing. In my head I can kind of understand the syntax with the WHERE clause a bit easier. Haven't found a real clear tutorial for the JOIN clause that would explain, still looking. Also, the JOIN clause has to be run using my MySQL workbench and not through a CFM page correct? – Brian Fleishman Sep 19 '14 at 15:23
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/61573/discussion-between-leigh-and-brian-fleishman). – Leigh Sep 19 '14 at 15:27

0 Answers0