1

using mysql 5.5.36

im trying to update the value for field1 in table1 based on 2 conditions - table1 field2 = valuex, table2 field3 = value-y.

i know i can update a field value based on a field value in the same table with this...

UPDATE table
SET field1 = new-value
WHERE field2 = value-x;

can i update based on 2 different values in 2 different tables using this?

UPDATE table1
SET field1 = new-value
WHERE field2 = value-x;
FROM table2
WHERE field3 = value-y;

maybe i need to be more literal. These are the actual table names, field names and conditions. i haven't tried to update data across 2 linked tables before...so this is a good exercise for me.

im updating the table qkt6v_propmid, field catid to value = 12.

the conditions are:

qkt6v_iproperty stype value = 1

qkt6v_propmid propid value = qkt6v_iproperty id value (this is the common ID between tables). in the data below it is ID 3044.

qkt6v_propmid catid = 2

here is how the actual tables / fields breakdown...

TABLE qkt6v_iproperty
id      stype
3044    1
3045    4

TABLE qkt6v_propmid

id      prop_id     cat_id
7968    3044        0
7969    3044        2
2
  • yes you can update from 2 table and 2 different values.. but you must have key to connect table1 and table2 Commented May 28, 2014 at 3:23
  • i understand what your saying. the 2 tables ARE linked together via an ID#. so i need to somehow add this condition: table2 field4 value = table1 field5 value. this is the ID# that matches the 2 tables together. how do i add that? Commented May 28, 2014 at 3:54

1 Answer 1

1

try this:

UPDATE table1,table2 SET table1.field1 = new-value WHERE table1.field2 = value-x AND table2.field3 = value-y and table1.ID = table2.ID

this will update table1 who have field field1 and link table1 and table2 via table1.ID and table2.ID

7
  • Please explain how some of this code works. This will discourage copying and pasting without understanding.
    – rayryeng
    Commented May 28, 2014 at 4:28
  • i edited / added a more thorough explanation with samples of the 2 tables and their respective fields. thanks for everyones help btw. Commented May 28, 2014 at 4:30
  • would any sql pro's care to check this for me?...does the syntax look correct? will this work. its updating 1 value in 1 table with 1 condition in that 1st table and 2 conditions in a different table where 1 condition is that the primary key for the 1st table matches an id in the 2nd table. i started to rearrange and it made more sense. maybe this works?UPDATE qkt6v_propmid SET cat_id = 12 WHERE cat_id = 2 FROM qkt6v_iproperty WHERE stype = 1; AND prop_id = id; Commented May 28, 2014 at 21:38
  • i need the condition that matches the primary key in table1 against a table2 id. it seems like some queries use a join and some use a sub query. is that right? Commented May 28, 2014 at 22:15
  • this is where i am now. i still get syntax error. im having issues. UPDATE qkt6v_iproperty_propmid SET cat_id = 12 WHERE cat_id = 2; FROM qkt6v_iproperty_propmid JOIN qkt6v_iproperty ON id = prop_id FROM qkt6v_iproperty WHERE stype = 1; Commented May 28, 2014 at 22:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.