Solved

MySQL strangeness

Posted on 2013-06-26
9
315 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 48

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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2012 R2 Express report problem 2 83
SQL Union 20 44
xpath sql query 2008 8 44
Update data using formula 22 23
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now