Updating a field with MySql statement

[bsnrjones]

I have two Tables in the same database. I need to set a field in TableA to 0 when the sku for that row is not found in TableB.

Here is what I have tried:
UPDATE TableA,TableB SET TableA.products_status='0' WHERE TableA.sku<>TableB.sku

Everytime I try this, it sets every products_status field to 0, even if the sku's match in each Table.

Any ideas? I must be making a simple mistake.

-edited to make i more clear that these are tables in the same database.

[edited by: bsnrjones at 8:21 pm (utc) on Mar. 5, 2004]

[ergophobe]

UPDATE table1 SET etc

You need to make separate connections for separate databases and you need separate update statements for separate tables.

I'm baffled that doesn't just cause a fatal error. Are you sure it isn't just throwing an error and then leaving the column as it is.

You need something like

SELECT table1.id from table1, table2 where table1.sku <> table2.sku

Loop through that and in your loop

UPDATE table1 SET table1.sku = 0 WHERE table1.id = [id from first query]

Perhaps you can do this with a subquery if you have a bleeding edge version of MySQL.

Tom

[edited by: ergophobe at 8:14 pm (utc) on Mar. 5, 2004]

[bsnrjones]

No error - just sets all to 0 in that column.

Let me clarify - I am trying to run this initially in phpmyadmin until I get the syntax right and then I will move it into my php script.

I think I understand what you are talking about, but would you mind writing out exactly what the statement would look like? ALSO, these are two tables in the same database. Does that help?

I am really dangerous with this stuff, but trying to learn.

Bsnrjones

[edited by: bsnrjones at 8:15 pm (utc) on Mar. 5, 2004]

[ergophobe]

Sorry, edited the original post while you were responding. Does my previous post answer it for you?

Tom

[bsnrjones]

kindof, but see my post above - I am trying to run this inside phpmyadmin.

Both tables are in the same database.

[ergophobe]

Can't do it unless the underlying mysql server allows subqueries (I believe that's still a beta-only feature).

You need to write a simple script to test it.

Don't use PhpMyAdmin unless you have to (so slow and cumbersome).

If you haven't already, install Apache, MySQL and PHP on your machine so you can run little test scripts (and big ones) in your own, safe little sandbox.

Cheers,

Tom

[coopster]

That is correct, ergophobe, subqueries start in 4.1, which is still in alpha release. MySQL 4.0 is the latest production release. However, starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables and the statements can use any type of join allowed in

SELECT

statements, such as

LEFT JOIN

. The issue you are having is that you are using a Cartesian [webmasterworld.com]¹ product. Try a

LEFT JOIN

and update only on

NULL

values:

UPDATE 
 TableA 
 LEFT JOIN TableB 
 ON TableA.sku = TableB.sku 
 SET TableA.products_status='0' 
 WHERE TableB.sku IS NULL 
; 

This will only work in MySQL >= 4.0.4.

¹Retracted statement, this is not a Cartesian product, see msg #10 below for explanation.

[edited by: coopster at 9:56 pm (utc) on Mar. 5, 2004]

[bsnrjones]

Thanks for your help! I tried the commands with the join, and it failed. Then I realized my SQL version is MySQL 4.0.15

So it is back to the drawing board. I have figured out a clunky work around for now.

Thanks again!

Burke

[ergophobe]

you are using a Cartesian product.

Indeed! First I got sidetracked by the "DatabaseA, DatabaseB" part, then realized he meant "TableA, TableB" and never really got to the WHERE clause:

TableA.sku <> TableB.sku

which is likely to be a pretty darned big record set if there are more than three products...

Coopster, you make more and more posts with "real" SQL and I realize that since pretty much all the SQL stuff I've done is with MySQL, I'm pretty limited in many ways when it comes to the real stuff. So much more efficient to do a single UPDATE query for which the DBMS is optimized than to SELECT result sets, loop through them in PHP and do an UPDATE query for every single record.

Every time I read one of your posts I think: "I've got to upgrade my SQL wetware to go beyond limitations encouraged by MySQL".

Tom

[coopster]

We all need to upgrade our SQL skillset. It's a continuous improvement process.

Actually I am incorrect in my statement as this is not a Cartesian product as there is a

WHERE

statement. It's an

INNER JOIN

. Even so, an

INNER JOIN

using a

WHERE

clause with negative equality like that is indeed going to return a very large result set. And that is why it set the status to 0 for every row in the table.

real SQL? If you mean standards, well, I try to adhere to standards but I really love MySQL and the extensions to the standards that the developers have implemented. I wouldn't say MySQL encourages limitations though, that's a bit harsh. If you are referring to the lack of support for subqueries and some other features -- well, I understand and share your disappointment. However, I think that for a free database it is quite fast, easy and fun to work with.

[ergophobe]

Don't get me wrong. I'm not knocking MySQL. I'm just knocking myself. I should *not* let the limitations of an excellent software package become limitations in my skill set, but I have.

Nothing wrong with that, as we can't know everything. However, it's a tough balance. Sometimes time invested in learning better skills now save so much time over the long term, but over the short term it can be difficult to study up as much as one would like.

And yes "real" was too harsh a term. I should have said what I meant - a better understanding of the full feature set of ANSI SQL, so that as MySQL becomes more and more compliant with the standard, I would be better positioned to take advantage.

Tom