Converting an MS Access Query to MySQL Query!

[glavster]

Hello can anyone help me convert the following MS Access Query to a MySQL pass through query.

INSERT INTO va_categories ( category_name, parent_category_id, category_path, is_showing )
SELECT First([va_c2k].SubCategory) AS [SubCategory Field], [va_c2k].cat1, "0," & [cat1] & "," AS category_path, "1" AS is_showing
FROM va_c2k
GROUP BY [va_c2k].cat1, "0," & [cat1] & ",", [va_c2k].SubCategory
HAVING (((Count([va_c2k].Category))>1) And ((Count([va_c2k].SubCategory))>1));

I can do most of it myself but I making a mistake around the "0," & [cat1] & "," AS category_path. What I go so far is this

INSERT INTO va_categories (category_name, parent_category_id, category_path, is_showing)
SELECT SubCategory AS 'SubCategory Field', cat1, '0,''va_c2k.cat1'',' AS category_path, '1' AS is_showing
FROM va_c2k
GROUP BY va_c2k.cat1, '0,''va_c2k.cat1'',', va_c2k.SubCategory
HAVING COUNT(va_c2k.Category)>1 AND COUNT(va_c2k.SubCategory)>1;

The problem is I not getting the value for field va_c2k.cat1 in the va_categories.category_path. It should read something like 0,6, but I just get 0,'cat1',.

Can anyone explain what i'm doing wrong.

Thanks,
Paul

 

[DonQuichote]

If you want to concat strings, use the CONCAT function. There is no concat operator (not a trustworthy one anyway) in MySQL*.

 ... AS 'SubCategory Field'

This is an identifier. So use identifier quotes. Using apostrophes here is an error.

If you double an apostrophe within other apostrophes, you use the standard SQL way of escaping an apostrophe. So if you insert 'O''clock', then O'clock will be the stored value.

* In fact there is a concat operator in standard SQL. But by default MySQL redefines it as a boolean OR operator. It all depends on how your server is configured.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

there is no FIRST() function in mysql, so use MIN() instead

INSERT 
  INTO va_categories 
     ( category_name
     , parent_category_id
     , category_path
     , is_showing )
SELECT MIN(SubCategory) 
     , cat1
     , CONCAT('0,' , cat1 , ',') 
     , 1 
  FROM va_c2k
GROUP 
    BY cat1
HAVING COUNT(Category) > 1
   AND COUNT(SubCategory) > 1

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[glavster]

Thanks guys for the help. Your code r937 nearly worked but not quite. It’s only inserting 9 entries instead of 70. This has to do with the fact that there are only 9 cat1 entries. But now that I've got the , CONCAT('0,' , cat1 , ',') bit I think I can solve the rest.

But don't fright as I'll be back with a query that uses a module, I know that is going to be an issue.

Thanks again,
Paul

 

[r937]

It's only inserting 9 entries instead of 70. This has to do with the fact that there are only 9 cat1 entries.

uh oh, it looks like i forgot to include SubCategory in the GROUP BY

INSERT 
  INTO va_categories 
     ( category_name
     , parent_category_id
     , category_path
     , is_showing )
SELECT MIN(SubCategory) 
     , cat1
     , CONCAT('0,' , cat1 , ',') 
     , 1 
  FROM va_c2k
GROUP 
    BY cat1
     , [red]SubCategory[/red]
HAVING Count(Category) > 1
   AND Count(SubCategory) > 1

although this makes MIN(Subcategory) irrelevant, yes?


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[glavster]

although this makes MIN(Subcategory) irrelevant, yes?"

Does it, I'm not sure. As I mentioned I've got a query with a module.

THIS IS THE QUERY

UPDATE va_c2k INNER JOIN va_categories ON (va_c2k.Sub_SubCategory=va_categories.category_name) AND (va_c2k.cat2=va_categories.parent_category_id) SET va_c2k.cat3 = [va_categories].[category_id]
WHERE ((([va_c2k].cat1)=SecondCommaValue([va_categories].[category_path])));

AND THIS IS THE MODULE

Function SecondCommaValue(strIn As String) As String
    Dim intFirstComma As Integer
    Dim intSecondComma As Integer
    intFirstComma = InStr(1, strIn, ",")
    If intFirstComma = 0 Then
        SecondCommaValue = ""
    Else
        intSecondComma = InStr(intFirstComma + 1, strIn, ",")
        If intSecondComma = 0 Then
            SecondCommaValue = Right(strIn, Len(strIn) - intFirstComma)
        Else
            SecondCommaValue = Mid(strIn, intFirstComma + 1, intSecondComma - intFirstComma - 1)
        End If
    End If
    'SecondCommaValue = Split(strIn, ",")(1)
End Function

What am I meant to do when converting to MySQL query.

Thanks,
Paul

 

[r937]

sorry, i don't do perl, or algol, or whatever that is ;-)

but it looks to me like you're splitting a string on a comma?

you'll probably be able to use mysql's SUBSTRING_INDEX function (look it up in da manual)



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[glavster]

No problem, I'll figure it out some how. And you are right I was splitting a string on a comma. There is one other thing you may be able to advise me on. is it possible to split a string field (text string) and compare the result with a number field. I keep getting a Type Mismatch.

Thanks again,
Paul

 

[r937]

yes, it's possible

mysql usually performs an implicit conversion for you, so i'm guessing "Type Mismatch" was your Access error message?

anyhow, you can do it like this ...

WHERE CAST(substring AS SIGNED) = number

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[glavster]

Hello again, wondering if I could get a little more help on my last query.

UPDATE va_c2k INNER JOIN va_categories ON (va_c2k.Sub_SubCategory = va_categories.category_name) AND (va_c2k.cat2 = va_categories.parent_category_id) SET va_c2k.cat3 = va_categories.category_id
WHERE va_c2k.cat1 = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX (va_categories.category_path, ',', 2),',',-1))  AS int ;

its fine until I get to here and then it get's beyond my skills;

WHERE va_c2k.cat1 = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX (va_categories.category_path, ',', 2),',',-1))  AS int ;

What I'm trying to do is make va_c2k.cat1 (which is a number field) equal to the second comma delimited value in va_categories.category_path (which is a text field) and make that value a number.

I just keep getting an error in syntax.

Thanks,
Paul

 

[r937]

you have [red]CAST( ... ) AS Int[/red]

it should be [blue]CAST( ... AS SIGNED)[/blue] where AS SIGNED designates an integer and goes inside the CAST()

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[glavster]

Thanks I tried something similar to that but used UNSIGNED.

UPDATE va_c2k INNER JOIN va_categories ON (va_c2k.Sub_SubCategory = va_categories.category_name) AND (va_c2k.cat2 = va_categories.parent_category_id) SET va_c2k.cat3 = va_categories.category_id
WHERE va_c2k.cat1 = CAST (SUBSTRING_INDEX (SUBSTRING_INDEX (va_categories.category_path, ',', 2), ',', -1)  AS UNSIGNED);

But I get a syntax error near 'UNSIGNED)' or for your's near 'SIGNED)'. I just can't figure it out.

Paul

 

[glavster]

You were right r937. The syntax was due to spaces in the scripting. It works now but I ran into another problem. When running another query I get the following "Duplicate entry '38' for key 1. This is the code,

INSERT INTO va_items ( item_code, manufacturer_code, item_name, trade_price, buying_price, stock_level, issue_date, preview_url, manufacturer_id, item_type_id, item_id )
SELECT va_c2k.item_code, va_c2k.manufacturer_code, va_c2k.item_name, va_c2k.trade_price, va_c2k.buying_price, va_c2k.stock_level, va_c2k.issue_date, va_c2k.preview_url, va_manufacturers.manufacturer_id, va_item_types.item_type_id, va_c2k.item_id
FROM (va_c2k INNER JOIN va_manufacturers ON va_c2k.manufacturer_name = va_manufacturers.manufacturer_name) INNER JOIN va_item_types ON va_c2k.Sub_SubCategory = va_item_types.item_type_name;

What I think is the problem is that even though the va_items table was emptied some auto_increment values still exist. How do I clear the auto_increment field back to 1.

Thanks,
Paul

 

[r937]

run [blue]TRUNCATE TABLE tablename[/blue] -- this not only empties the table faster than a DELETE, but also resets the auto_increment

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[glavster]

That reset the auto_increment but I still get the same error.

Duplicate entry '38' for key 1 (#1062)

Any oidea what this error means.

Thanks for all your help so far, I think I'll give it a rest till morning.

Paul

 

[r937]

it means you are trying to add a second row with a value for a column that's defined as unique

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[DonQuichote]

You started with a query that had a GROUP BY clause. I suspect that was because of a unique column in the destination table. The query you have now does not have this GROUP BY clause and therefore does not yield unique results.

So you can choose to add a GROUP BY clause or tell MySQL to ignore inserting the rows if that would violate a uniqueness constraint (INSERT IGNORE INTO <tablename> ...)



+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[glavster]

OK the problem is that the only field involved in this query that has NO Duplicates set or is a Unique field is the item_id field which is an auto_increment in the va_c2k and va_items tables. I'm trying to populate the va_items table with data from the va_c2k table. I've checked the va_c2k table for dupicates in the item_id field and there are none.

There are 2 other tables that I use to populate va_items table and they are va_item_types and va_manufacturers. Both these tables also have an auto_increment field but they are not set to unique in the va_items table.

So I can't figure out why the query stops at "Duplicate entry '38' for key 1 (#1062)" every time.

Paul

 

[DonQuichote]

Look at the table's definition (SHOW CREATE TABLE va_items) and see what the first index is. Now that field already contains 38 and you are trying to insert it again, or the query returns 38 for that field more than once. That is essentially the whole problem.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)