• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

T SQL Update table.col based on several criteria

I have a query that selects records from orderline table where the flagfield = 'L' and provides the commenttext from the ordercomment table for the same line item.
I want to update a field in the orderline table and set it to the commenttext1 value in the ordercomment table with several criteria in place:  the ordernumber in the line and comment table are equal; the orderlinesequence numbers in the line and comment table are equal; the orderline flagfield = 'L' and the ordercomment commenttext1 value is not null.
Attaching select statement that returns intersection of the records ...
MANY MANY THANKS IN ADVANCE!


--select statement that returns the intersection of the
--records and the values from each table
SELECT      A.SOPNUMBE, A.LNITMSEQ, A.ITEMNMBR, B.SOPNUMBE, B.LNITMSEQ, B.COMMENT_1 
FROM        SOP10200 A
LEFT        OUTER JOIN SOP10202 B ON A.SOPNUMBE = B.SOPNUMBE AND A.LNITMSEQ = B.LNITMSEQ 
WHERE       A.SOPNUMBE = '2050092' AND
            A.COMMNTID  = 'L' AND 
            B.COMMENT_1 IS NOT NULL

Open in new window

0
tqsneed
Asked:
tqsneed
1 Solution
 
autosblindoCommented:
If I understood well what you want you have to use and UPDATE query with a join.

Try this:
UPDATE SOP10200
SET SOP10200.FieldName = SOP10202.COMMENT_1
FROM SOP10200 INNER JOIN SOP10202 ON SOP10200.SOPNUMBE = SOP10202.SOPNUMBE AND SOP10200.LNITMSEQ = SOP10202.LNITMSEQ
WHERE SOP10200.COMMNTID  = 'L' AND SOP10202.COMMENT_1 IS NOT NULL

Bye.
0
 
tqsneedAuthor Commented:
wow...let me give that a try!!!!
0
 
tigin44Commented:
try this
UPDATE A 
SET A.columnName = B.COMMENT_1  
FROM        SOP10200 A
		INNER JOIN SOP10202 B ON A.SOPNUMBE = B.SOPNUMBE AND A.LNITMSEQ = B.LNITMSEQ 
WHERE       A.SOPNUMBE = '2050092' AND
            A.COMMNTID  = 'L' AND 
            B.COMMENT_1 IS NOT NULL

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now