Update a table from a query in another table

Question

UPDATE devicenumplanmap 
SET devicenumplanmap.e164mask = '+1' 
FROM devicenumplanmap 
WHERE (select devicenumplanmap.pkid, devicenumplanmap.e164mask 
       from devicenumplanmap, device 
       where devicenumplanmap.fkdevice = device.pkid 
         and device.name like '%RDP' 
         and devicenumplanmap.e164mask like '819%')

A syntax error has occurred.

We want to update the table devicenumplanmap from a search in another table.

UPDATE devicenumplanmap SET devicenumplanmap.e164mask = '+1' FROM devicenumplanmap WHERE exists(select * from devicenumplanmap, device where devicenumplanmap.fkdevice = device.pkid and device.name like '%RDP' and devicenumplanmap.e164mask like '819%') — Ice, May 30 '13 at 20:43
possible duplicate of [subqueries in UPDATE SET (sql server 2005)](http://stackoverflow.com/questions/2586517/subqueries-in-update-set-sql-server-2005) — RandomSeed, Mar 02 '14 at 14:01

sgeddes · Answer 1 · 2013-05-30T20:51:43.813

1

It looks like you're trying to UPDATE with a JOIN. Depending on your RDBMS, the syntax is slightly different.

MySQL:

UPDATE devicenumplanmap d
    JOIN device de ON d.fkdevice = de.pkid
SET d.e164mask = '+1' 
WHERE de.name like '%RDP' 
       AND d.e164mask like '819%'

SQL Server:

UPDATE  d
SET d.e164mask = '+1' 
FROM devicenumplanmap d 
    JOIN device de ON d.fkdevice = de.pkid
WHERE de.name like '%RDP' 
       AND d.e164mask like '819%'

More universal approach:

UPDATE devicenumplanmap 
SET e164mask = '+1' 
WHERE e164mask like '819%' AND
    fkdevice IN (
        SELECT pkid
        FROM Device 
        WHERE name like '%RDP' 
)

edited May 30 '13 at 20:51

answered May 30 '13 at 20:45

sgeddes

62,311
6
61
83

Well! Thank you so much for that! It worked like a charm! I simply added concat ! It was a pleasure! UPDATE devicenumplanmap SET e164mask = concat('+1',e164mask) WHERE e164mask like '418%' AND fkdevice IN ( SELECT pkid FROM Device WHERE name like '%RDP' ) – Danik W. Therrien May 31 '13 at 12:26
@DanikW.Therrien -- np, glad I could help! – sgeddes May 31 '13 at 12:27

score 0 · Answer 2 · answered May 30 '13 at 20:44

Try something like that:

UPDATE devicenumplanmap 
SET devicenumplanmap.e164mask = '+1' 
FROM devicenumplanmap 
WHERE **exists(select ***
       from devicenumplanmap, device 
       where devicenumplanmap.fkdevice = device.pkid 
         and device.name like '%RDP' 
         and devicenumplanmap.e164mask like '819%')

score 0 · Answer 3 · answered May 30 '13 at 20:50

0

This should work for, easy and simple

UPDATE devicenumplanmap
SET devicenumplanmap.e164mask = '+1' 
FROM devicenumplanmap dp 
INNER JOIN device d
ON dp.fkdevice = d.pkid
WHERE d.name like '%RDP' 
and dp.e164mask like '819%'

answered May 30 '13 at 20:50

Vijay Pote

103
1
1
7

Update a table from a query in another table

3 Answers3