Jump to content
Sign in to follow this  
Mike

Stock Levels & Cost price

Recommended Posts

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

 

Share this post


Link to post
Share on other sites

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)?

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.. 

 

Edited by Mike

Share this post


Link to post
Share on other sites

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;

 

Edited by bsmither

Share this post


Link to post
Share on other sites

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 '='

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...