0

I know this question have been asked before, My previous similar question was dismissed cause there already is several like this.. but i just dont get this to work...

Ive tried this (in PHPmyAdmin) to update cell24 with value from table2 to table1

UPDATE name_table1 A
SET 'A.cell24' FROM 'B.cell24'
FROM name_table2 B
WHERE A.id = B.id
AND A.cell24 = NULL

But this cause "#1064 - You have an error in your SQL syntax;....."

And ive tried this:

UPDATE name_table1  A
SET A.cell24 = ( SELECT B.cell24
                 FROM name_table2 B )

And this works BUT it removes all values in the cell24 on other id's thats not supposed to be updated. Only the cell24 with equal id in both tables was correct updated. Rest was left blank

UPDATE name_table1 A
SET A.cell24 = ( SELECT B.cell24
                 FROM name_table2 B
                WHERE A.id = B.id AND A.cell24 = NULL )

This caused all cell24 in table1 to be blank

Please help :P

(i would also need to know how i make this update with more cells in one query, ex i need to update cell24, cell25, cell26 IF the cell is empty (NULL) )

swe
  • 1,416
  • 16
  • 26
Milla
  • 23
  • 6
  • Since you mention phpMyAdmin, I will assume that this is nothing to do with SQL Server which you have tagged? – DavidG Nov 05 '14 at 14:00

2 Answers2

1

You can find your exact answer here: How can I do an UPDATE statement with JOIN in SQL?

The syntax here is given for oracle, mssql, mysql

copy from that answer:

update ud u inner join sale s on u.id = s.udid set u.assid = s.assid

so it should be

update ud u
inner join sale s on
    u.id = s.udid
set u.assid = s.assid, u.secondfield = s.secondfield

for multi-column

In your case it should be:

UPDATE name_table1 A
INNER JOIN name_table2 B ON
A.id = B.id
AND A.cell24 = NULL
SET A.cell24 = B.cell24, A.cell25 = B.cell25

sorry, but i have no environment to test this at the moment

Community
  • 1
  • 1
swe
  • 1,416
  • 16
  • 26
  • the problem with these examples is that i really dont get what to change to my credentials :( im sorry for being a noob :P – Milla Nov 05 '14 at 14:17
0

Your syntax is slightly off:

 UPDATE A
 SET cell24 = B.cell24
 FROM name_table1 A 
    JOIN name_table2 B
       ON A.id = B.id
 WHERE A.cell24 IS NULL

To update multiple cells, you could try some more complex SQL update statements, but the simplest and most reliable thing to do would be to execute successive UPDATE statements for each column you want to update. The reason for this is that the WHERE controls what record you update, and if you did something like this:

UPDATE A
SET cell24 = B.cell24
  , cell25 = B.cell25
..
WHERE  A.cell24 is null or A.cell25 is null

... you'd update both columns when either column was null for that record.

Ann L.
  • 13,760
  • 5
  • 35
  • 66