Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to update one column value to another in same table Expand / Collapse
Author
Message
Posted Saturday, June 21, 2014 7:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:50 AM
Points: 61, Visits: 83
my table payment_details structure is

payment_id payment_code
1 null
2 null
3 null
4 null

here payment_id is a primary key and i need to update the whole payment_id column to payment_code column
so i just tried the below query
update payment_details
set payment_code = payment_no
where payment_code is null
but it shows subquery error,can anyone say?? loads of thanks in advance
Post #1584580
Posted Saturday, June 21, 2014 7:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 1,551, Visits: 4,283
neethu payal (6/21/2014)
my table structure is

payment_id payment_code
1 null
2 null
3
4


I feel there might possibly be something missing in the question, hint (?).
Post #1584582
Posted Saturday, June 21, 2014 7:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 1,551, Visits: 4,283
Now we have the full question, here is the answer.

Your query is missing the FROM part.

--my table payment_details structure is
DECLARE @payment_details TABLE
(
payment_id INT PRIMARY KEY CLUSTERED NOT NULL
,payment_code INT NULL
);
INSERT INTO @payment_details (payment_id)
VALUES (1),(2),(3),(4)

UPDATE P
SET P.payment_code = P.payment_id
FROM @payment_details P
WHERE P.payment_code IS NULL

SELECT
*
FROM @payment_details


Results
payment_id  payment_code
----------- ------------
1 1
2 2
3 3
4 4
Post #1584584
Posted Tuesday, June 24, 2014 11:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:26 PM
Points: 87, Visits: 217
But there is no need to add a from clause as it was working fine.

See the code below:

--my table payment_details structure is
DECLARE @payment_details TABLE
(
payment_id INT PRIMARY KEY CLUSTERED NOT NULL
,payment_code INT NULL
);
INSERT INTO @payment_details (payment_id)
VALUES (1),(2),(3),(4)

UPDATE @payment_details
SET payment_code =payment_id
WHERE payment_code IS NULL

SELECT
*
FROM @payment_details

Result:


payment_id payment_code
----------- ------------
1 1
2 2
3 3
4 4



Kindly, share the exact error..
Post #1585742
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse