webressurs
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:
Like this:
ne = 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 :)
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)
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
.
.
.
As you see I need to do different kind of 'Updates' in tblUserProfileData based on the result from the 'Select' statement, where tblUserProfileData.CellPhoI 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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--
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.tblUserProfileDa ta
SET [CMS].dbo.tblUserProfileDa ta.Payment Remark = t1.PaymentRemark
FROM [CMS].dbo.tblUserProfileDa ta as t inner join
(select CASE WHEN ((tblMessage.SentWithOpera tor = '4711' AND tblMessage.ErrorCode = '1010') OR (tblMessage.SentWithOperat or = '4713' AND MS_Out_Log.ErrorCode = '22')) THEN '2'
WHEN (tblMessage.SentWithOperat or = '4712' AND MS_Out_Log.ErrorCode = '9202') THEN '11'
ELSE PaymentRemark
END as PaymentRemark from [CMS].dbo.tblUserProfileDa ta WHERE UserCategoryID = @UserCategoryId AND CellPhone = tblMessage.Tel
) t1 on t1.Primary_key=t.primary_k ey
SET [CMS].dbo.tblUserProfileDa
FROM [CMS].dbo.tblUserProfileDa
(select CASE WHEN ((tblMessage.SentWithOpera
WHEN (tblMessage.SentWithOperat
ELSE PaymentRemark
END as PaymentRemark from [CMS].dbo.tblUserProfileDa
) t1 on t1.Primary_key=t.primary_k
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
ASKER
I did only test this solution since it worked perfect. Thanks for all help :)
ASKER
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.
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
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.
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.
ASKER
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
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
ASKER
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?