1
Table1     Column1     Value1     Table2     Column2        Value2
-------------------------------------------------------------------
tbl_start   DESC        blue      tbl_end     Description     red
tbl_job     JOB         Doctor    tbl_role    JOB             Surgeon

I am trying to create an SQL stored procedure which will update tables based on the mapping table (I have created the one above as an example)

I have tried a few variations but can't seem to get my head around how the query will be written

For example:

UPDATE @TABLE2 SET @Column2 = @Value2 Where @Value2 = @Value1

So say tbl_end had the value 'blue' in the Description column, after the stored procedure runs it should have updated all instances of 'blue' to 'red'

In the same way if tbl_role had the value 'Doctor' in the JOB column, after the store procedure runs it should have changed 'Doctor' to 'Surgeon'

Thank you for any help, suggestions or comments

I appreciate any feedback

(Not worrying about security risks in this instance)

neeko
  • 1,930
  • 8
  • 44
  • 67

1 Answers1

1

It's a classic case of UPDATE with JOIN statement:

UPDATE Table1
SET Table2.Value2 = Table1.Value1
FROM Table1 JOIN Table2 ON Table1.Column1 = Table2.Column2

See this links for more details:

SQL update query using joins , How can I do an UPDATE statement with JOIN in SQL?

Edit:

If you want to update multiple tables- since you can't update more that one table in a one statement - You will have to run this code against every table you wish to update.

Community
  • 1
  • 1
Yosi Dahari
  • 6,794
  • 5
  • 24
  • 44
  • Thanks, however, the table names are variable as you see from the table I have drawn up, the update statement will have to be able to update multiple tables? E.g. updating all 'Table2' (tbl_end, tbl_role) in the column specified replacing its current value with the value2 if it currently equals value 1, if that makes sense? – neeko Oct 08 '13 at 15:08
  • Is it not possible with a stored procedure? – neeko Oct 08 '13 at 15:30
  • It's possible with/without SP, but not in single statement. – Yosi Dahari Oct 08 '13 at 15:35