Mike Posted October 28, 2018 Share Posted October 28, 2018 Could anyone help me with this please, I have spent a good 6 hours trying to get this query right but with still no luck. I have a table that I have imported a csv file into as it contains stock levels and cost prices. The table it created on an IMPORT from CSV file is called 'TABLE 70' it contains the following fields: 'COL 1' (product id) 'COL 2' (not used) 'COL 3' (stock level) 'COL 4' (cost) 'COL 5' (not used) I want to update all the product stock levels in the inventory table with new stock figures from 'TABLE 70' - COL 3 where product_id = COL 1 My coding is as follows but I cannot get it to work. UPDATE cctl_CubeCart_inventory LEFT JOIN 'TABLE 70' ON cctl_CubeCart_inventory.product_code = 'TABLE 70.COL 1' SET cctl_CubeCart_inventory.stock_level = 'TABLE 70.COL 3'; If there is an easier way to update the stock level field for each product then please let me know. The TABLE 70 is just from an import of a CSV file. I will also want the cost field to be updated also but have just been trying to get the stock level sorted for now. Any help would be appreciated. Regards Mike Link to comment Share on other sites More sharing options...
bsmither Posted October 28, 2018 Share Posted October 28, 2018 I will suggest that table names and column names use underscores to represent breaks in words -- not spaces. I will also warn to use backticks (shifted squiggle key) instead of apostrophes to delineate tables and columns, although backticks are sometimes not necessary if the table and column name is not ambiguous - as when using underscores. For COL 1, this holds the product_id (a key that the rest of the database makes reference to), or the product_code (an arbitrary part number designation)? Link to comment Share on other sites More sharing options...
Mike Posted October 28, 2018 Author Share Posted October 28, 2018 Sorry, its the product code not the id. When I did the import, it just created the table and columns. Once I can get the script working then I will change that and make it better. I wanted to use the standard import but just for the stock level and cost price but when I tried that importing from a CSV file it wiped out all the products. This was my only other way I could think of. So basically I need stock levels to be updated when product number matches from both tables. Thanks in advance bsmither. Link to comment Share on other sites More sharing options...
Mike Posted October 28, 2018 Author Share Posted October 28, 2018 I have now changed the table name as you where right it 'TABLE 70' was causing an issue. I now have: UPDATE cctl_CubeCart_inventory LEFT JOIN a_tmp_table ON cctl_CubeCart_inventory.product_code = a_tmp_table.COL1 SET cctl_CubeCart_inventory.stock_level = a_tmp_table.COL3; Still not working. Link to comment Share on other sites More sharing options...
bsmither Posted October 28, 2018 Share Posted October 28, 2018 Let's review what COL1 is holding: The product_id is an integer and the value in this column is auto-incremented for every new product added to the table. The product_code is an arbitrary sequence of characters that has no meaning to CubeCart's operations. This statement: ON cctl_CubeCart_inventory.product_code = a_tmp_table.COL1 is associating (presumably) the product_id in COL1 with the arbitrary product_code in cctl_CubeCart_inventory. Link to comment Share on other sites More sharing options...
Mike Posted October 28, 2018 Author Share Posted October 28, 2018 This is now the table that contains the update: sku Stock StockLevel Price FullPrice AA6109 In Stock 5 7.17 7.17 AB6108 In Stock 2 7.17 7.17 AC6137 In Stock 9 4 4 My script is now : UPDATE cctl_CubeCart_inventory LEFT JOIN a_tmp_table ON cctl_CubeCart_inventory.product_code = a_tmp_table.sku SET cctl_CubeCart_inventory.stock_level = a_tmp_table.StockLevel; The product code in cubecart is AA6109, AB6108 etc.. Link to comment Share on other sites More sharing options...
bsmither Posted October 28, 2018 Share Posted October 28, 2018 Things are clearer now. According to examples given in the MySQL docs, try this query: UPDATE cctl_CubeCart_inventory,a_tmp_table SET cctl_CubeCart_inventory.stock_level=a_tmp_table.StockLevel WHERE cctl_CubeCart_inventory.product_code=a_tmp_table.sku; Link to comment Share on other sites More sharing options...
Mike Posted October 29, 2018 Author Share Posted October 29, 2018 Thanks bsmither for your help and patients. I'm now getting this error: #1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' Link to comment Share on other sites More sharing options...
Mike Posted October 29, 2018 Author Share Posted October 29, 2018 RESOLUTION: change from utf8_general_ci to utf8_unicode_ci in a_tmp_table. This seems allow it to run and update. Further test need looking at just to see if everything is getting updated as it should. Thanks for your help bsmither. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.