Solved

MySQL strangeness

Posted on 2013-06-26
9
317 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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