SQL update in a select statement

Question

my problem is: I need to select all my db-tables which contain a column NrPad out of my database and for exactly this tables I need to update the column NrPad

I have already a working select and update statement:

select
    t.name as table_name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where c.name like 'NrPad'

Update Anlage Set NrPad = CASE WHEN Len(Nr) < 10 THEN '0' + Convert(Nvarchar,Len(Nr)) ELSE Convert(Nvarchar,Len(Nr)) END + Nr

My problem is: How can I merge this two statements together?

I'm open to suggestions and your help is greatly appreciated.

You didn't say what dbms you are using, In case its oracle than the answer is in your question, you have MERGE statement which updates a table by another table or select in your case — sagi, Jan 19 '16 at 08:16
Update `NrPad` in all tables to add another `0`? You need a cursor and create the UPDATE using dynamic SQL. — dnoeth, Jan 19 '16 at 08:16
like dnoeth said cursor is like the only solution, oracle merge statement is just update by another table not update multiple tables — Raffaello.D.Huke, Jan 19 '16 at 08:42

score 0 · Answer 1 · edited May 23 '17 at 11:47

0

Not testet on your case but you could do an update - set - from - where.

Have a look at this question with multiple answers: How do I UPDATE from a SELECT in SQL Server?

edited May 23 '17 at 11:47

Community

1
1

answered Jan 19 '16 at 08:11

Indregaard

1,195
1
18
26

Raffaello.D.Huke · Answer 2 · 2016-01-20T06:05:15.540

0

maybe someone will judge me,but all i can do for this case is cursor

    DECLARE @table_name varchar(100)
    DECLARE @sql varchar(1000)
    DECLARE table_cursor CURSOR FOR 
     select
       t.name as table_name
     from sys.tables t
     inner join sys.columns c
             on t.object_id = c.object_id
          where c.name like 'NrPad'
   OPEN table_cursor
   Fetch next From table_cursor Into @table_name
    While @@fetch_status=0     
   Begin
     set @sql = 'Update' + @table_name + 'Set NrPad = CASE WHEN Len(Nr) < 10 THEN '0' + Convert(Nvarchar,Len(Nr)) 
                        ELSE Convert(Nvarchar,Len(Nr)) END + Nr'
     EXEC (@sql)
   Fetch Next From table_cursor Into @table_name
   End   
   Close table_cursor   
   Deallocate table_cursor

this is how you write the cursor in SQLSERVER, i really don't want code another one for Oracle. so please tag the dbms you are using next time

edited Jan 20 '16 at 06:05

answered Jan 19 '16 at 08:40

Raffaello.D.Huke

552
2
9

Sorry, I totally forgot about the dbms I'm using - it's Microsoft SQLServer 2014. Thank you for your solution with the cursor. The only problem is, that I cannot use table names as variables (right?). I tried your solution and the only problem is that @table_name after UPDATE "Must declare the table variable "@table_name". – KatharinaG Jan 19 '16 at 12:12
oh,it's my bad not using dynamic SQL,i edited the answer and tested it,it will be fine. you could try it again @user1474960 – Raffaello.D.Huke Jan 20 '16 at 03:32
@user1474960 glad that help if you could please accept it :) – Raffaello.D.Huke Jan 20 '16 at 07:18

Thanh Nhan · Answer 3 · 2016-01-24T23:45:00.910

0

You can modify the select statement to generate the update statements then execute them all.

Below uses string literal of Oracle.

select 'Update ' || t.name || q'[ Set NrPad = CASE WHEN Len(Nr) < 10 THEN '0' + Convert(Nvarchar,Len(Nr)) ELSE Convert(Nvarchar,Len(Nr)) END + Nr;]'
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where c.name like 'NrPad'

edited Jan 24 '16 at 23:45

answered Jan 19 '16 at 08:49

Thanh Nhan

453
6
17

score 0 · Accepted Answer · answered Jan 20 '16 at 03:05

Use the INFORMATION_SCHEMA rather than sys.tables, and create a dynamic SQL statement like so:

DECLARE @sql varchar(max) = '';

SELECT
    @sql = @sql + '; UPDATE ' + c.TABLE_NAME + ' SET NrPAd = CASE WHEN LEN(Nr)<10 THEN ''0'' + CONVERT(NVARCHAR,LEN(NR)) ELSE CONVERT(NVARCHAR,LEN(NR)) END + Nr'
FROM INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'NrPad'


print @sql -- for debugging purposes
exec (@sql)

This assumes that all tables that have the NrPad column also have a Nr column. If you need to check for those, or if you just need to use the Nr column from a particular table, it's a bit different (either join against INFORMATION_SCHEMA.COLUMNS again or against Anglage to get the value of Nr or check that Nr is a column on that table).

No prob - if you found it helpful, consider marking it as the accepted answer (the check under the vote rocker). — Dan Field, Jan 20 '16 at 13:28

SQL update in a select statement

4 Answers4