CODEWITHSUNDEEP
mysqlsql-update
user2828211
user2828211

Reputation: 11

sql query update field based on value in 2 tables

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

Upvotes: 1

Views: 1169

Answers (1)

Ronald Alexander Kailola
Ronald Alexander Kailola

Reputation: 149

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

Upvotes: 1

Related Questions