0

I am trying to update a column in table, based on data (that is constantly updated) in a temporary table (via csv imports). The date in the temporary table is VARCHAR ("2/20/2014 10:29:25 AM" format) and the date in the table I want to update is in DATETIME type.

I'm not sure how to properly join these two tables to update the dates in the permanent table based on the current date showing in the temp table...using product_sku (in both tables) as the joining field.

Here is what I have tried, but keep getting SQL error (' #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM...)

Update t0 
set t0.product_available_date = 
               (SELECT STR_TO_DATE( `t1.product_available_date`,
                                    '%c/%e/%Y %H:%i' ))
                FROM cpg5443_virtuemart_products as t0 
                join cpg5443_virtuemart_products_temp as t1
                on t0.product_sku = t1.product_sku
                Where t0.product_sku='002-765-AS'

Do you see any glaring mistakes and can you advise how I can correct? In the end, I will want the where statement to say "where t0.product_sku=t1.product_sku" with hopes this will update this column for every record based on the date in the temp table. For now, I'm just testing the update with one product_sku.

Tim S
  • 91
  • 3
  • 13

1 Answers1

0

Looks like you are missing parentheses after '002-765-AS'. Have you posted all the code? Also you have an extra parentheses after the date format.

Try this:

update cpg5443_virtuemart_products as t0
inner join cpg5443_virtuemart_products_temp t1 
on t0.product_sku = t1.product_sku
set t0.product_available_date = str_to_date (t1.product_available_date, 
                                              '%c/%e/%Y %H:%i:%s')
where t0.product_sku = '002-765-AS'
  • Something is still missing I think (I am now getting the error that says "#1146 - Table[account_dbname].t0 doesn't exist". Yes, I have posted all of the code I had tried. Do I need the sub "Select" ? Any idea what I am missing? – Tim S Feb 20 '14 at 17:12
  • Well, the error is saying that the table doesn't exist. Make sure you are running the query in the correct database, that is, where this table t0 exists. –  Feb 20 '14 at 17:14
  • yes, the table exists. Yes, I'm running this in the correct database. t0 being cpg5443_virtuemart_products. Something is wrong with my update logic I do believe. – Tim S Feb 20 '14 at 17:17
  • Let me try to better explain what I'm trying to do. I have 2 tables with 3 columns each (all by the same name). product_sku, product_in_stock, and product_available_date. The only difference in the temp table is that the date is varchar ("2/20/2014 10:29:25 AM" format). I want to update the permanent table with the inventory and date that exists in the temp table, when matching the "product_sku" field in both tables. Am I attempting to do this correctly? – Tim S Feb 20 '14 at 17:21
  • Yes, that's a reasonable thing to do. In the first case you were getting a 1064 error which we have fixed. Now you are getting a 1146 error. That is something else. Have you or anyone updated your database server? Look at this: http://stackoverflow.com/a/8410497/3248346 –  Feb 20 '14 at 17:38
  • You may need to recreate your database and restart your mysql service –  Feb 20 '14 at 17:39
  • also I am assuming that your string in the temp table which contains the date matches the declared string format i.e. '%c/%e/%Y %H:%i'. –  Feb 20 '14 at 17:44
  • i've updated the query to not use the sub-query. Also it seems as though you used backticks here: `t1.product_available_date`. –  Feb 20 '14 at 17:50
  • added the seconds to the string formatter as well ;) –  Feb 20 '14 at 18:01
  • Using your revised code above, I am getting the error: " #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from cpg5443_virtuemart_products as t0 join cpg5443_virtuemart_products_temp as t1' at line 4 – Tim S Feb 20 '14 at 18:09
  • try the updated query; i come from a sybase background; mysql does things in a different way. –  Feb 20 '14 at 18:23
  • your original update query was wrong; you were trying to update t0 that's why we had the message that the table doesn't exist because that is just an alias for the cpg5443_virtuemart_products table. –  Feb 20 '14 at 18:24
  • ok thanks for that. if I change the where clause to say: "where t0.product_sku=t1.product_sku" Will that update all records in the table that have a match in the temp table? – Tim S Feb 20 '14 at 18:57
  • you already have that match in the "on" clause - that bit is fine. –  Feb 20 '14 at 18:58
  • So I don't need to define "Where" at all then? – Tim S Feb 20 '14 at 18:59
  • Think of the 'where' clause as a further refinement in the data you want returned. In your original query you had this "where t0.product_sku = '002-765-AS'", so I guess you still want that. If you don't then you can take it out. But I included it in my refactored query as it was in your original query. –  Feb 20 '14 at 19:01
  • 1
    That was only there to test this update on just one record. I do not want to limit this update to one record now that I know the logic works. Now I want to remove that requirement and update all records that have a match in the temp table. If I'm understanding you correctly, I would simply remove the "where" clause, right? – Tim S Feb 20 '14 at 19:04
  • thanks. last question...can I also update another column at the same time, but just adding another "set" line? Like this: "set t0.product_available_date = str_to_date(t1.product_available_date, '%c/%e/%Y %H:%i:%s') set t0.product_in_stock=t1.product_in_stock" – Tim S Feb 20 '14 at 19:10
  • Yes you can. You don't need to specify the 'set' keyword though. Just separate with commas e.g. "set t0.product_available_date = str_to_date(t1.product_available_date, '%c/%e/%Y %H:%i:%s'), t0.product_in_stock=t1.product_in_stock". –  Feb 20 '14 at 19:13
  • that's great! didn't know you could do that – Tim S Feb 20 '14 at 19:13