Select and Update

jkirkerx

I'm trying to consolidate 3 operations into 1 Not sure if it's possible, but it's seems like it is. I want to select some data, and take the data from 1 table and update it to another table. Her's what I have. It's kind of a soup at the moment, experienenting with different ideas. In my Create Table, I can't figure out how to use those value in the update in place of the parameters. Perhaps I should just use the line in Update, the select for CardLabel.

DECLARE @CardID INT;
SET @CardID = 645;
CREATE TABLE #CCInfo
(
Label VarChar(40)
, CardBrand VarChar(80)
, CardExpMonth VarChar(80)
, CardExpYear VarChar(80)
, CardHolder_FirstName VarChar(80)
, CardHolder_LastName VarChar(80)
, CardNum VarChar(80)
, CardPhoneNum VarChar(20)
)
INSERT INTO #CCInfo
(
Label
, CardBrand
, CardExpMonth
, CardExpYear
, CardHolder_FirstName
, CardHolder_LastName
, CardNum
, CardPhoneNum
)
SELECT
Label
, CardBrand
, CardExpMonth
, CardExpYear
, CardHolder_FirstName
, CardHolder_LastName
, CardNum
, CardPhoneNum
FROM CardInfoPerm
WHERE CardID=@CardID
UPDATE
OrderInfo
SET
CardID=@CardID
, Cardlabel= (SELECT Label FROM CardInfoPerm WHERE CardID=@CardID)
, CardBrand=Card_
, CardExpMonth=@CardExpMonth
, CardExpYear=@CardExpYear
, CardHolder_FirstName=@CardHolder_FirstName
, CardHolder_LastName=@CardHolder_LastName
, CardNum=@CardNum
, CardPhoneNum=@CardPhoneNum
, PaymentInfo=@CardID
, ApprovalCode=''
, AuthorizationID=''
, CheckID=''
, CheckLabel=''
, CheckType=''
, CheckName=''
, BankName=''
, CheckRoutingNum=''
, CheckAccountNum=''
WHERE OrderNum='CA-2054'

jkirkerx

Here I get must declare the scalar @CCInfo in the UPDATE Section I understand what it means, I just don't know how to go about getting the scalar to be recognized in Update. The first part works, just having trouble with the update.

DECLARE @CardID INT, @OrderNumber VarChar(40);
SET @CardID = 645;
SET @OrderNumber = 'CA-2054';

DECLARE @CCInfo TABLE
(
Label VarChar(40)
, CardBrand VarChar(80)
, CardExpMonth VarChar(80)
, CardExpYear VarChar(80)
, CardHolder_FirstName VarChar(80)
, CardHolder_LastName VarChar(80)
, CardNum VarChar(80)
, CardPhoneNum VarChar(20)
);

INSERT @CCInfo
(
Label
, CardBrand
, CardExpMonth
, CardExpYear
, CardHolder_FirstName
, CardHolder_LastName
, CardNum
, CardPhoneNum
)
SELECT
Label
, CardBrand
, CardExpMonth
, CardExpYear
, CardHolder_FirstName
, CardHolder_LastName
, CardNum
, CardPhoneNum
FROM CardInfoPerm
WHERE CardID=@CardID

UPDATE
OrderInfo
SET
CardID=@CardID
, Cardlabel=@CCInfo.Label
, CardBrand=@CCInfo.CardBrand
, CardExpMonth=@CCInfo.CardExpMonth
, CardExpYear=@CCInfo.CardExpYear
, CardHolder_FirstName=@CCInfo.CardHolder_FirstName
, CardHolder_LastName=@CCInfo.CardHolder_LastName
, CardNum=@CCInfo.CardNum
, CardPhoneNum=@CCInfo.CardPhoneNum
, PaymentInfo=@CardID
, ApprovalCode=''
, AuthorizationID=''
, CheckID=''
, CheckLabel=''
, CheckType=''
, CheckName=''
, BankName=''
, CheckRoutingNum=''
, CheckAccountNum=''
WHERE OrderNum=@OrderNumber

PIEBALDconsult

Separate the statements with semicolons?

jkirkerx

I went for the later in this function, but found out later that I could of just declared and set all the card data first and then run the UPDATE using the variables. So I ended up with this, I shortened it to keep it more brief

UPDATE " & _
OrderInfo " & _
SET
CardID=@CardID
, Cardlabel=(SELECT Label FROM CardInfoPerm WHERE CardID=@CardID)
, CardBrand=(SELECT CardBrand FROM CardInfoPerm WHERE CardID=@CardID)
, CardExpMonth=(SELECT CardExpMonth FROM CardInfoPerm WHERE CardID=@CardID)
, CardExpYear=(SELECT CardExpYear FROM CardInfoPerm WHERE CardID=@CardID)
WHERE OrderNum=@OrderNum"

But later the next day I ended up writing something that does everything in one shot, far beyond I could imagine that I can do. So I modified the above to this below

DECLARE " & _
@CardLabel VarChar(80)
, @CardBrand VarChar(80)
, @CardExpMonth VarChar(80)
, @CardExpYear VarChar(80)
, @New_OrderNumber VarChar(80);

SET @CardLabel = (SELECT Label FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardBrand = (SELECT CardBrand FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardExpMonth = (SELECT CardExpMonth FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardExpYear = (SELECT CardExpYear FROM CardInfoPerm WHERE CardID=@CardID);

IF EXISTS(SELECT * FROM OrderInfo WHERE OrderNum=@OrderNum)
BEGIN
UPDATE
END
ELSE
BEGIN
INSERT
END

Then, I was able to do more, delete previous cart items, generate a new order number, copy the cart contents over return the new order number all in one shot now, use to take me like 8 functions to do it all. I'm amazed at how fast it runs now. I would post it, but it's too large I'm stoked on how it came out, and learned a lot on my own. I think I got the semi-colons right this time, I need to read up on that.

MeutherOlaf

The Select-Statement have to run into a variable. For all recorset in the variable you have to carry out the update statement