CODEWITHSUNDEEP
sqlsql-serverselect
rjbogz
rjbogz

Reputation: 870

Updating data from multiple tables based on a row in another table

I have a table called fields that looks like this:

 Name          | Table
---------------+----------------
 DateFound     | Table1
 DateUpdate    | Table2
 DateCharge    | Table3
 DateLost      | Table4
 DateDismissed | Table5

And what I want to do is change the year for all of those fields in their specified table to 2013. They are all datetime fields in their respective table. So basically, I want to have DateFound to be changed in Table1 from 06/12/2009 16:14:23 to 06/12/2013 16:14:23.

Is there an easy to do this by saying something like:

SELECT (SELECT [Name]
          FROM fields)
  FROM (SELECT [Table]
          From fields)

Upvotes: 0

Views: 68

Answers (3)

Bit
Bit

Reputation: 1078

I have not tested this, but I think you can run this for each table/field it will do the trick.

UPDATE Table1
SET DateFound = DATEADD(YY, DATEDIFF(YY, DateFound, getdate()), DateFound)

Upvotes: 1

Sunny
Sunny

Reputation: 4809

Try with cursor and dynamic sql, something like this.

DECLARE @changeYear datetime
SET @changeYear = '2013-01-01'
DECLARE @tableName varchar(50), @columnName varchar(50)

Declare updateTime_Cursor Cursor FOR
   select name, table from fields

OPEN updateTime_Cursor

FETCH NEXT FROM updateTime_Cursor
  INTO @columnName, @tableName

WHILE @@FETCH_STATUS = 0
BEGIN

      DECLARE @sql nvarchar(1000)
      SELECT @sql = 'Update dbo.'+@tablename+' set '+@columnName+' = DATEADD(yy,  DATEDIFF(yy, '+@columnName+', @changeYear), '+@columnName+')'
      EXEC sp_executesql @sql, N'@changeYear datetime',changeYear
END

CLOSE updateTime_Cursor
DEALLOCATE updateTime_Cursor

Upvotes: 1

Peter Alfvin
Peter Alfvin

Reputation: 29399

You didn't indicate what dialect of SQL you were using, but you could generate a mysql script to do what you want with the following:

select concat('update ',table_name, ' set ', column_name, ' = DATE_FORMAT(',
  column_name,', ''2013-%m-%d %T''', ');')
  from my_table

per this sqlfiddle

Upvotes: 0

Related Questions