Multiple Update 1

[WarcraftPlayer]

I am not sure going about doing this. In my proc I need to update a table the only thing is sometimes its a single update and other times it is a multiple update so basically I need to merge these 2 togther and am not quite sure how to do it.

update tableA Set CustomerInfo = Getdate() Where customerid = @CustomerID

update tabelA Set CustomerInfo = Getdate() From tableB where LotsOfCustomers = 'Multiple'

Both of these queries work seperate but when I try to merge them with a where clause or When I get an error

I want something like this, I know this doesnt work at least not the way I have it.

UPDATE TableA SET CustomerInfo = GetDate() 
FROM TableB
WHERE CASE WHEN @CustomerID IS NOT NULL THEN Customerid = @Customerid
ELSE
LotsOfCustomers = 'Multiple'
END

The variable @Customerid gets passed in and if does not get passed in it means there are mulitple people to update.

Thanks in advance.

 

[jbenson001]

Why not use an IF statement?:

If @CustomerID is not NULL
'Run Update statement 1
Else
'Run Update statement 2

Jim

 

[WarcraftPlayer]

Not quite what I was looking for. I thought of that already. I want it to be in all in one query. So if there is update to the query I will only have to change it in one place.

 

[jbenson001]

It will all be in one place, your stored procedure.

 

[maswien]

XtremeGator,

A little confusing in your second query:

update tabelA Set CustomerInfo = Getdate() From tableB where LotsOfCustomers = 'Multiple'

Do you really mean:

update tabelA Set CustomerInfo = Getdate() 
From  tabelA inner join tableB 
 on tabelA.customerid = tableB.customerid  
where tableB.LotsOfCustomers = 'Multiple'

?

If that is true, try following:

UPDATE TableA 
SET CustomerInfo = getdate()
FROM TableA inner join TableB
on TableA.customerid = TableB.customerid
WHERE 
 (@Customerid is not null
    and customerid = @Customerid) 
   or
 (@Customerid is not null 
     and TableB.LotsOfCustomers = 'Multiple')

 

[maswien]

sorry for the typo, it should be:

UPDATE TableA 
SET CustomerInfo = getdate()
FROM TableA inner join TableB
on TableA.customerid = TableB.customerid
WHERE 
 (@Customerid is not null
    and customerid = @Customerid) 
   or
 (@Customerid is null 
     and TableB.LotsOfCustomers = 'Multiple')