Link to home
Start Free TrialLog in
Avatar of webressurs
webressursFlag for Norway

asked on

Select from one table and update another related table

I need to update records in one table (tblUserProfileData) based on the 'Select' result from another table (tblMessage).

First, I do a 'Select' in tblMessage to get all the records I want:
SELECT tblMessage.Optional2 AS TypeId, tblMessage.Tel, tblMessage.ok, tblMessage.ErrorCode, tblMessage.SentWithOperator
FROM tblMessage INNER JOIN tblType ON tblMessage.Optional2 = tblType.Id
WHERE  (tblMessage.ok = '0') AND (tblMessage.Optional2 = @TypeId) AND (CONVERT (CHAR(10), tblMessage.SentDate, 110) = @SentDate)

Open in new window

Now, I need to update records in tblUserProfileData based on the 'Select' result above.
Like this:
IF (tblMessage.SentWithOperator = '4711' AND tblMessage.ErrorCode = '1010') OR (tblMessage.SentWithOperator = '4713' AND MS_Out_Log.ErrorCode = '22')
BEGIN
	UPDATE [CMS].dbo.tblUserProfileData SET PaymentRemark = '2' WHERE UserCategoryID = @UserCategoryId AND CellPhone = tblMessage.Tel
END

IF tblMessage.SentWithOperator = '4712' AND MS_Out_Log.ErrorCode = '9202'
BEGIN
	UPDATE [CMS].dbo.tblUserProfileData SET PaymentRemark = '11' WHERE UserCategoryID = @UserCategoryId AND CellPhone = tblMessage.Tel
END

.
.
.

Open in new window

As you see I need to do different kind of 'Updates' in tblUserProfileData based on the result from the 'Select' statement, where tblUserProfileData.CellPhone = tblMessage.Tel.


I don't know the best way to solve this. Maybe I should create a temporary table with the result from tblMessage. Then I can loop through this table and do each update based on this result?

Thanks for all tips :)
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of webressurs

ASKER

Hi!

UserCategoryID is in tblUserProfileData.

Users in tblUserProfileData shall be updated where UserCategoryID = @UserCategoryID and Cellphone = [Cellphone from tblMessage]...

It seems like this is correct in your expample?
Seems like I guessed right then.  I would strongly suggest that you TEST the SQL as a SELECT first.  i.e.
-- UPDATE t
-- SET 
SELECT 
PaymentRemark
, -- = 
CASE WHEN (v.SentWithOperator = '4711' AND v.ErrorCode = '1010') 
                           OR (v.SentWithOperator = '4713' AND v.ErrorCode = '22') THEN '2'
                         WHEN v.SentWithOperator = '4712' AND v.ErrorCode = '9202' THEN '11'
                         ELSE PaymentRemark
                    END
FROM tblUserProfileData t
INNER JOIN (SELECT tblMessage.Optional2 AS TypeId, tblMessage.Tel, tblMessage.ok, tblMessage.ErrorCode, tblMessage.SentWithOperator
            FROM tblMessage 
            INNER JOIN tblType ON tblMessage.Optional2 = tblType.Id
             WHERE  (tblMessage.ok = '0') AND (tblMessage.Optional2 = @TypeId) AND (CONVERT (CHAR(10), tblMessage.SentDate, 110) = @SentDate)) v
        ON t.CellPhone = v.Tel AND UserCategoryID = @UserCategoryId

Open in new window

AND do a BACKUP before applying an UPDATE like that until you know it works as expected.
Try this
BEGIN TRAN

UPDATE t
SET t.PaymentRemark = CASE WHEN (tm.SentWithOperator = '4711' AND tm.ErrorCode = '1010')
                           OR (tm.SentWithOperator = '4713' AND tm.ErrorCode = '22') THEN '2'
                         WHEN tm.SentWithOperator = '4712' AND tm.ErrorCode = '9202' THEN '11'
                         ELSE '0' -- default value
                    END
FROM tblUserProfileData t
INNER JOIN tblMessage tm
ON t.CellPhone = tm.Tel
INNER JOIN tblType tt ON
tm.Optional2 = tt.Id
WHERE t.UserCategoryID = @UserCategoryId
AND tm..ok = '0'
AND tm.Optional2 = @TypeId
AND (CONVERT (CHAR(10), tm.SentDate, 110) = @SentDate)


COMMIT TRAN

--SelvaS--
UPDATE [CMS].dbo.tblUserProfileData
SET [CMS].dbo.tblUserProfileData.PaymentRemark = t1.PaymentRemark
FROM  [CMS].dbo.tblUserProfileData  as t inner join
(select  CASE WHEN ((tblMessage.SentWithOperator = '4711' AND tblMessage.ErrorCode = '1010') OR (tblMessage.SentWithOperator = '4713' AND MS_Out_Log.ErrorCode = '22')) THEN '2'
                         WHEN (tblMessage.SentWithOperator = '4712' AND MS_Out_Log.ErrorCode = '9202') THEN '11'
                         ELSE PaymentRemark
                    END as PaymentRemark from [CMS].dbo.tblUserProfileData WHERE UserCategoryID = @UserCategoryId AND CellPhone = tblMessage.Tel
) t1 on t1.Primary_key=t.primary_key
Considering that you want to update PaymentRemark column only, don't need to list all the other columns there. Also I would put the case in the subquery. So here's my suggestion

UPDATE a
set a.PaymentRemark = b.PaymentRemark
FROM CMS].dbo.tblUserProfileData a
inner join (
	SELECT 	tblMessage.Tel, 
		case when (tblMessage.SentWithOperator = '4711' AND tblMessage.ErrorCode = '1010') OR (tblMessage.SentWithOperator = '4713' AND tblMessage.ErrorCode = '22') then '2'
		     when (tblMessage.SentWithOperator = '4712' AND tblMessage.ErrorCode = '9202') then '11'
		end as PaymentRemark
	FROM tblMessage 
	INNER JOIN tblType ON tblMessage.Optional2 = tblType.Id
	WHERE  	(tblMessage.ok = '0') AND 
		(tblMessage.Optional2 = @TypeId) AND 
		(CONVERT (CHAR(10), tblMessage.SentDate, 110) = @SentDate)
) b on a.UserCategoryID = @UserCategoryID and a.CellPhone = b.Tel and b.PaymentRemark is not null

Open in new window

I did only test this solution since it worked perfect. Thanks for all help :)
Hi!

It seems to be a little problem with the code. Let's say that SMS_Out_Log contains 2 records with the same cellphone number; one that meets the "Select Case" criteria and one that don't.

By default the PaymentRemark is NULL. In the case above the PaymentRemark should be updated with the value from the "Select case" since one record meets the criteria. The other record that don't meet the criteria should not do anything.

But, the PaymentRemark is not updated at all - the PaymentRemark is still NULL. If I delete the record that don't meet the "Select Case" criteria and run the Stored Procedure again the PaymentRemark is correct updated! It seems like the record that don't meet the "Select Case" criteria in some way "override" the update...?

Any clue?

Please see attached (and updated) code.

ALTER PROCEDURE [dbo].[spSetPaymentRemark]
(
	@TypeId int,
	@UserCategoryId smallint,
	@CountryCode int
)
AS
BEGIN

	SET NOCOUNT ON;

	IF @CountryCode = 46
	BEGIN
		SET @CountryCode = '' -- Sweeden: ContryCode is stored in tblUserProfileData
	END


	-- TEST
	-- SELECT l.Tel, l.SentWithOperator, l.ErrorCode, u.PaymentRemark , PaymentRemark,

	-- RUN
	UPDATE u SET PaymentRemark = 
		CASE
			WHEN (l.SentWithOperator = '1' OR l.SentWithOperator = '4711') AND l.ErrorCode = '1010' THEN '2'
			WHEN (l.SentWithOperator = '2' OR l.SentWithOperator = '4712') AND l.ErrorCode = '9204' THEN '2'
			ELSE PaymentRemark -- Default value
		END
		FROM tblUserProfileData u 
			INNER JOIN SMS_Out_log l ON CAST(CAST(@CountryCode AS VARCHAR) + CAST(u.CellPhone AS VARCHAR) AS VARCHAR) = CAST(l.Tel AS VARCHAR)
		WHERE u.UserCategoryID = @UserCategoryId
			AND l.ok = '0'
			AND l.Optional2 = @TypeId
			AND (CONVERT (CHAR(10), l.SentDate, 110) = CONVERT (CHAR(10), GETDATE() - 1, 110))

END

Open in new window

Sorry ... been very sick the last few days.

I am guessing that the second SMS is being joined to and passes the WHERE clause too.  It sound like that this because it also seems that it is being processed last of the two records and, as it falls to the CASE ... ELSE, updated the field back to null.

If you do not want records being used in the UPDATE ... they must be eliminated via the join or where clause, probably where cause in this case.
Thanks for your reply, hope you are doing well now :)

I made a new question at EE and got this solution that seems to work perfect:
https://www.experts-exchange.com/questions/27875175/Update-records-in-one-table-based-on-result-from-another-table.html