I have two tables with structure

No | Name | IDNo | HouseNoBlock

IDNo | HouseNo | Block

I am trying to update the HouseNoBlock Column of first table with value joined by HouseNo/Block while comparing the IDNo . I was able to get the values using the query

SELECT CONCAT(table2.HouseNo,'/',table2.Block) 
FROM   table2 ,table1 
WHERE  table1.IDNo = table2.IDNo

but when I try to UPDATE table1 I am getting a Syntax error ERROR 1064 (42000) .

UPDATE table1 
SET    HouseNoBlock = CONCAT (table2.HouseNo,'/',table2.Block) 
FROM   table2 ,table1 
WHERE  table1.IDNo = table2.IDNo

Can somebody help with this?


Since you have not supplied any data, I've prepared next example:

create table tbl1 (No int, Name varchar(200), IDNo int, HouseNoBlock varchar(200));

create table tbl2 (IDNo int, HouseNo varchar(100), Block varchar(100));

insert into tbl1 values 
(1, 'Name1', 1, null), 
(2, 'Name2', 2, null), 
(3, 'Name3', 1, null);

insert into tbl2 values 
(1, 'House1', 'Block1'), 
(2, 'House2', 'Block2');

Use next sentence:

UPDATE tbl1  --< first the table that will be updated
JOIN   tbl2
ON     tbl1.IDNo = tbl2.IDNo
SET    HouseNoBlock = CONCAT(tbl2.HouseNo,'/',tbl2.Block);

This is final result:

No | Name  | IDNo | HouseNoBlock 
-: | :---- | ---: | :------------
 1 | Name1 |    1 | House1/Block1
 2 | Name2 |    2 | House2/Block2
 3 | Name3 |    1 | House1/Block1

dbfiddle here

Have a look at MySQL docs about UPDATE syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. Each matching row is updated once, even if it matches the conditions multiple times. For multiple-table syntax, ORDER BY and LIMIT cannot be used.

  • Can you please clarify on which table to use first on a comparison WHERE clause . – Anoop D Mar 23 '18 at 14:19
  • 1
    The table that will be updated. – McNets Mar 23 '18 at 14:20
  • My tbl1 is really large with some 18 billion rows and i need this join to be restricted using a WHERE condition on tbl1 at the end of query , but still it is traversing whole rows , will adding the WHERE condition to join with an AND will work . – Anoop D Mar 24 '18 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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