Update-Statement doesn't work... 1

[bateman23]

Hi,
i'm just going nuts trying to fugure out why my sql-statement isn't working. Perhaps i'm just blind....

I've got the following two tables:

mysql> describe keyword;
+------------+
| Field      | Type 
+------------+
| id_keyword | int(10)      
| schlagwort | varchar(255)
| sprache    | char(3) 
+----------------+


mysql> describe link_keyword;
+----------------+
| Field           | Type
+----------------+
| id_link_keyword | int(10) 
| id_keyword      | int(10)   
| projekte_nr     | int(10)     
| stamp           | timestamp(14) 
| tmp_keyword     | text    
| tmp_fingerprint | text        
+----------------+

In the table "link_keyword" i'm trying to update the "id_keyword". It should contain the number (id_keyword in keyword) of the corresponding Keyword (which is now stored in "tmp_keyword")....
Perhaps there are jsut too many keywordss , but i don't know what's wrong with my sql-statement:

UPDATE link_keyword, keyword SET link_keyword.id_keyword = keyword.id_keyword WHERE keyword.schlagwort = "Frühindikatoren" AND link_keyword.id_link_keyword = "6"

 

[rac2]

Vielleicht mussen Sie das JOIN condition machen. Entschuldige mich. Maybe you need to add the JOIN condition to the WHERE clause.

UPDATE link_keyword, keyword SET
    link_keyword.id_keyword = keyword.id_keyword
WHERE
  link_keyword.id_keyword = keyword.tmp_keyword
  AND keyword.schlagwort = "Frühindikatoren"
  AND link_keyword.id_link_keyword = "6"

 

[excession]

When comparing text/strings you should try using the LIKE keyword instead of the = sign

 

[ericbrunson]

The LIKE keyword is only useful if you are using wildcard characters (% and _). Otherwise the optimizer treats it identically to an equality.

 

[bateman23]

rac2, thanx a lot for the hint. The following code turns out to be right:

UPDATE link_keyword, keyword 
   SET link_keyword.id_keyword = keyword.id_keyword 
   WHERE keyword.schlagwort = "Frühindikatoren" 
     AND link_keyword.projekte_nr = "6" 
     AND link_keyword.tmp_keyword = keyword.schlagwort;

---------------------------------------
Visit me @:

http://www.daniel-purucker.de/