?
Solved

MySQL strangeness

Posted on 2013-06-26
9
Medium Priority
?
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…

752 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