Solved

MySQL strangeness

Posted on 2013-06-26
9
314 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
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.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

15 Experts available now in Live!

Get 1:1 Help Now