?
Solved

MySQL strangeness

Posted on 2013-06-26
9
Medium Priority
?
325 Views
Last Modified: 2013-06-26
Hi,

I have what I thought was a straightforward query to edit existing records and drop 6 more values in from an older table where both tables contain a product_id column with matching values. I've tried several variations and I keep getting the same error: Unknown column 'srp_final.jos_mijoshop_product.product_id' but I'm staring at that column right now. It definitely exists!

Is there something wrong with my query syntax? Here it is:

EDIT srp_final.jos_mijoshop_product;insert into srp_final.jos_mijoshop_product (author, publisher, presenter, format, artist, numberofpages) select authshow, publisher, presenter, format, artist, numberofpages from selene_river_press_com.jos3_hikashop_product where srp_final.jos_mijoshop_product.product_id=selene_river_press_com.jos3_hikashop_product.product_id

Any advice? Why won't this work?

Thanks

Bill
0
Comment
Question by:billium99
  • 4
  • 3
  • 2
9 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39278139
EDIT is not a valid SQL command.  Not sure what this is for:

EDIT srp_final.jos_mijoshop_product;

Open in new window


The rest of this should be okay, but let's format your SQL so that it is easily readable (by me, at least):

INSERT INTO srp_final.jos_mijoshop_product (
	author
	, publisher
	, presenter
	, format
	, artist
	, numberofpages
	)
SELECT authshow
	, publisher
	, presenter
	, format
	, artist
	, numberofpages
FROM selene_river_press_com.jos3_hikashop_product
WHERE srp_final.jos_mijoshop_product.product_id =
        selene_river_press_com.jos3_hikashop_product.product_id
;

Open in new window


Which looks okay to me.  Can you run and attach the output of these 2 commands:

DESC srp_final.jos_mijoshop_product;
DESC selene_river_press_com.jos3_hikashop_product;

Open in new window

0
 
LVL 1

Author Comment

by:billium99
ID: 39278181
OK the two files are attached

Thanks for your help

Bill
queries.zip
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39278183
nice "comma first" formatting :) {my preference also}

maybe "EDIT srp_final.jos_mijoshop_product;"
is intened to be...

UPDATE srp_final.jos_mijoshop_product .... ?

but in the context of the overall SQL one would not prefix an insert with an update.
i.e. I think it is redundant here.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 23

Expert Comment

by:nemws1
ID: 39278248
Sorry - duh.  Should have seen this right away.  You need to reference any tables you might might be joining FROM in the FROM statement.  Instead, you have a new table reference in the WHERE section.  Try this:

INSERT INTO srp_final.jos_mijoshop_product (
	author
	, publisher
	, presenter
	, format
	, artist
	, numberofpages
	)
SELECT selProd.authshow
	, selProd.publisher
	, selProd.presenter
	, selProd.format
	, selProd.artist
	, selProd.numberofpages
FROM selene_river_press_com.jos3_hikashop_product AS selProd
    JOIN srp_final.jos_mijoshop_product AS srpProd
        ON srpPRod.product_id =
                selProd.product_id
;

Open in new window


RE: formatting - I use Poor Man's TSQL formatter in SSMS (even for MySQL code sometimes!).  Wish I had a nice SQL formatter for UNIX (haven't found a decent one, though).  I fell in love with comma-first formatting the instant I started using it. :)
0
 
LVL 1

Author Comment

by:billium99
ID: 39278265
Gotcha - yeah my queries are often patched together from various websites. Somewhere, it was recommended to start with "edit". In any case, many queries I've fired seem to be OK with edit, update, and the redundancy inherent in my approach. But I will make sure to avoid that. Thanks for the advice.

Any idea why the query itself is giving me an error about an unknown column?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39278284
I thought I recognized that format [like this]

I use this a lot for E-E questions: http://poorsql.com/

for larger work with sql I use my copy of sqlinform (java) (commercial)
but there is a free online version (limited by size of sql)
http://www.sqlinform.com/
0
 
LVL 1

Author Comment

by:billium99
ID: 39278437
OK I got an error: Field 'model' doesn't have a default value - which tells me we are creating new records. That's what INSERT is trying to do, no?

I'm trying to update existing records with 6 new values and pull those values, if they exist, from an older table.

Sorry - my original code could have been wrong as well. Should I be using an UPDATE query instead? I can't seem to modify your query above and get it to work.
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 2000 total points
ID: 39278607
OK I got an error: Field 'model' doesn't have a default value - which tells me we are creating new records. That's what INSERT is trying to do, no?

Yup.  It's going to be adding in new rows.

Sorry - my original code could have been wrong as well. Should I be using an UPDATE query instead? I can't seem to modify your query above and get it to work.

Re-reading your original description, maybe it is an UPDATE you're after.  These product_ids should already exist in the srp_final.jos_mijoshop_product table, correct?  If so, you're after something like this:

UPDATE srp_final.jos_mijoshop_product AS srpProd
	JOIN selene_river_press_com.jos3_hikashop_product AS selProd
		ON srpPRod.product_id = selProd.product_id
SET srpProd.author = selProd.authshow
	, srpProd.publisher = selProd.publisher
	, srpProd.presenter = selProd.presenter
	, srpProd.format = selProd.format
	, srpProd.artist = selProd.artist
	, srpProd.numberofpages = selProd.numberofpages;
;

Open in new window

0
 
LVL 1

Author Closing Comment

by:billium99
ID: 39278665
Boom! That did it - thanks for the help...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question