0

this query is working but not giving me the output I want.

I am expecting to have 'FAILED' when one of the two conditions is met at least one time through the rows in the columns [TVF] or [RPPS]. But here I have always 'OK' as output in my updated table. Normally, I should have 'FAILED' for the row [AFUP] and 'OK' for the row [BICHATP].

Can you run it on your machine? 

drop table [MyTable]
drop table [BICHATP]
drop table [AFUP]


CREATE TABLE [AFUP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);

CREATE TABLE [BICHATP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);
INSERT INTO [AFUP] ([TVF], [RPPS]) VALUES
    ('M006', '1010'), ('M054','1000'), ('M015', '2324');

INSERT INTO [BICHATP] ([TVF], [RPPS]) VALUES
    ('M000', '1101'), ('M090','1001'), ('M012', '1023');

CREATE TABLE [MyTable]
(
    [Tabs] nvarchar(100),
    [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Tabs]) VALUES
    ('AFUP'), ('BICHATP');


DECLARE @cmd nvarchar(max);
DECLARE @Tabs nvarchar(10);

DECLARE cur1 CURSOR FOR
    SELECT [Tabs] FROM [MyTable];

OPEN cur1;

FETCH NEXT FROM cur1 INTO @Tabs
WHILE(@@FETCH_STATUS = 0)
BEGIN
    SET @cmd = 'update [MyTable]
                set [Check_column] = 
                    case when left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'' then ''FAILED''
                         else ''OK''
                    end
               from DB_SANOFI.dbo.' + @Tabs;

    EXEC sp_executesql @cmd;

    FETCH NEXT FROM cur1 INTO @Tabs
END

CLOSE cur1;
DEALLOCATE cur1;

select *
from MyTable

Tables

    AFUP           BICHATP
------------     ------------
M006 | 1010      M000 | 1101
M054 | 1000      M090 | 1001
M015 | 2324      M012 | 1023

Expected result:

Tabs    | Check column
-----------------------
   UFAP | FAIL
BICHATP | OK
Improve this question
3
  • The UPDATE statement has no WHERE clause so both rows are updated each iteration. Also, the source has 3 rows so the results are not deterministic. I think you want a WHERE clause so that only one row is updated each iteration and an EXISTS subquery in the CASE expression. Add expected results to your question for clarification. Commented Nov 22, 2018 at 13:56
  • I updated the questions according to your remarks. What do you mean by "not deterministic"? Commented Nov 22, 2018 at 15:22
  • By not deterministic I mean results will be random because of the multiple rows in the FROM clause. EXISTS (or NOT EXISTS) correlated with a WHERE clause ensures consistent results for each execution. Commented Nov 22, 2018 at 15:48

1 Answer 1

Reset to default
1

If I understand your question correctly, I think you want OK in the MyTable if you cannot find any row on the joined tables that meets your FAILED conditions. I'm using NOT EXISTS and also made sure I'm only updating MyTable for the specific table being processed.

Let me know if I have misunderstood your requirements.

drop table if exists [MyTable]
drop table if exists [BICHATP]
drop table if exists [AFUP]


CREATE TABLE [AFUP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);

CREATE TABLE [BICHATP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);
INSERT INTO [AFUP] ([TVF], [RPPS]) VALUES
    ('M006', '1010'), ('M054','1000'), ('M015', '2324');

INSERT INTO [BICHATP] ([TVF], [RPPS]) VALUES
    ('M000', '1101'), ('M090','1001'), ('M012', '1023');

CREATE TABLE [MyTable]
(
    [Tabs] nvarchar(100),
    [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Tabs]) VALUES
    ('AFUP'), ('BICHATP');


DECLARE @cmd nvarchar(max);
DECLARE @Tabs nvarchar(10);

DECLARE cur1 CURSOR FOR
    SELECT [Tabs] FROM [MyTable];

OPEN cur1;

FETCH NEXT FROM cur1 INTO @Tabs
WHILE(@@FETCH_STATUS = 0)
BEGIN
    SET @cmd = 'update [MyTable]
                set [Check_column] = case when NOT EXISTS
                    (SELECT * FROM ' + @Tabs + ' where
                    left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'')
                    then ''OK''
                         else ''FAILED''
                    end
               from ' + @Tabs + ' WHERE [MyTable].Tabs = ''' + @Tabs + '''';

               print @cmd
    EXEC sp_executesql @cmd;

    FETCH NEXT FROM cur1 INTO @Tabs
END

CLOSE cur1;
DEALLOCATE cur1;

select *
from MyTable

| Tabs    | Check_column |
|---------|--------------|
| AFUP    | FAILED       |
| BICHATP | OK           |
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.