February 12, 2009 at 5:30 am
hello again
I´ve 2 databases MANDIFLEX and PRODIFLEX
I need to update a field in database MANDIFLEX when some condition in PRODIFLEX is true
I came with this query, wich returns want i want
select mandiflex.dbo.tabat.st_tabatphc,mandiflex.dbo.tabat.cod_art,prodiflex.dbo.st.ref from
tabat,prodiflex.dbo.st where cod_art=prodiflex.dbo.st.ref
and prodiflex.dbo.st.familia='MP208'
i need to do this
update mandiflex.dbo.TABATOPCO set U_NCONSUMO = 1
where ( the condiction from the select above??)
please help
February 12, 2009 at 5:39 am
update mandiflex.dbo.TABATOPCO set U_NCONSUMO = 1
FROM mandiflex.dbo.tabat.st_tabatphc,mandiflex.dbo.tabat.cod_art,prodiflex.dbo.st.ref from
mandiflex.dbo.tabat inner join ,prodiflex.dbo.st on mandiflex.dbo.tabat.cod_art=prodiflex.dbo.st.prodiflex.dbo.st.ref and S.prodiflex.dbo.st.familia='MP208'
"Keep Trying"
February 12, 2009 at 10:54 am
hello the query returns
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
i´v tried this
update tabatopco set u_nconsumo=1 where (select ref,u_nconsumo,cod_art,desc_art
from tabatopco(nolock),tabat(nolock) inner
join prodiflex.dbo.st on mandiflex.dbo.tabat.cod_art=prodiflex.dbo.st.ref
where prodiflex.dbo.st.familia='MP208')
but all the records U_NCONSUMO became = 1 the where condition didin´t work
February 12, 2009 at 11:15 am
carlos cachulo (2/12/2009)
hello the query returnsServer: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
i´v tried this
update tabatopco set u_nconsumo=1 where (select ref,u_nconsumo,cod_art,desc_art
from tabatopco(nolock),tabat(nolock) inner
join prodiflex.dbo.st on mandiflex.dbo.tabat.cod_art=prodiflex.dbo.st.ref
where prodiflex.dbo.st.familia='MP208')
but all the records U_NCONSUMO became = 1 the where condition didin´t work
You have a serious problem with your query. In the subquery you are mixing ANSI and old style joins. You are joining the tables tabat and prodiflex using an ANSI style inner join, but you are also join this to tabatopco using an old style join (note the comma between tabatopco and tabat) but don't have any criteria for that join in the where clause. You really should try using just ANSI style joins in your queries.
February 12, 2009 at 11:25 am
I have rewritten your first select query below. Next question, however, is how does this query relate to the other table, tabatopco.
What would really help is if you would read the first article I have referenced below in my signature block regarding asking for assistance and follow the guidlines in that article to post the DDL for your tables, sample data, and expected results. You would get a lot more beneficial help if you did so.
select
t.st_tabatphc,
t.cod_art,
s.ref
from
mandiflex.dbo.tabat t
inner join prodiflex.dbo.st s
on (t.cod_art = s.ref)
where
s.familia = 'MP208'
February 12, 2009 at 11:41 am
ok, thank you very much
February 12, 2009 at 11:48 am
At a guess:
UPDATE mandiflex.dbo.TABATOPCO
SET U_NCONSUMO = 1
WHERE EXISTS
(
    SELECT *
    FROM mandiflex.dbo.tabat T
    WHERE EXISTS
        (
            SELECT *
            FROM prodiflex.dbo.st S
            WHERE T.cod_art = S.ref
                AND S.familia ='MP208'
        )
        -- assumming link between tabat and TABATOPCO is st_tabatphc in both tables
        AND T.st_tabatphc = mandiflex.dbo.TABATOPCO.st_tabatphc
)
February 12, 2009 at 11:58 am
Start from the begining
2 DATABASES - MANDIFLEX and PRODIFLEX
MANDIFLEX has this tables TABATOPCO and TABAT
PRODIFLEX has the table ST
TABATOPCO and TABAT can me joined by this fields tabat.st_tabatphc=tabat.stamp
and TABAT can be joined to PRODIFLEX.DBO.ST by cod_art=prodiflex.dbo.st.ref
ex: select cod_art,prodiflex.dbo.st.ref from tabat,prodiflex.dbo.st
returns : cod_art ref
------------------ ------------------
0000CP420002 0000CP420002
0001 0000CP420002
0002 0000CP420002
there is no possible relation between TABATPCO with ST.
I´ve this query A :
select mandiflex.dbo.tabat.st_tabatphc,mandiflex.dbo.tabat.stamp,mandiflex.dbo.tabat.cod_art,prodiflex.dbo.st.ref from
tabat,prodiflex.dbo.st where cod_art=prodiflex.dbo.st.ref
and prodiflex.dbo.st.familia='MP208'
wich returns:
st_tabatphc stamp cod_art ref
------------------------- ------------------------- ------------------ ------------------
PPA2KQ10663X3691050277866 PPA2KQ10663X3691050277866 0005023 0005023
PPA1PQ0L5I421262263298320 PPA1PQ0L5I421262263298320 0061 0061
PUS2JX0PKVZE1845946971080 PUS2JX0PKVZE1845946971080 01260 01260
PUS2JX0ORYDL1731810264135 PUS2JX0ORYDL1731810264135 01277 01277
PUS2JX0OMEKV1710323326516 PUS2JX0OMEKV1710323326516 104031 104031
PUS2K01085133702171401835 PUS2K01085133702171401835 11302 11302
PUS2JX0LJHI01309067655480 PUS2JX0LJHI01309067655480 114139 114139
ok, this are de ones i want to update,
so my question is how can i update a field in TABATPCO where the condition from my query A is true
thanks
February 12, 2009 at 12:13 pm
carlos cachulo (2/12/2009)
Start from the begining2 DATABASES - MANDIFLEX and PRODIFLEX
MANDIFLEX has this tables TABATOPCO and TABAT
PRODIFLEX has the table ST
TABATOPCO and TABAT can me joined by this fields tabat.st_tabatphc=tabat.stamp
and TABAT can be joined to PRODIFLEX.DBO.ST by cod_art=prodiflex.dbo.st.ref
ex: select cod_art,prodiflex.dbo.st.ref from tabat,prodiflex.dbo.st
returns : cod_art ref
------------------ ------------------
0000CP420002 0000CP420002
0001 0000CP420002
0002 0000CP420002
there is no possible relation between TABATPCO with ST.
I´ve this query A :
select mandiflex.dbo.tabat.st_tabatphc,mandiflex.dbo.tabat.stamp,mandiflex.dbo.tabat.cod_art,prodiflex.dbo.st.ref from
tabat,prodiflex.dbo.st where cod_art=prodiflex.dbo.st.ref
and prodiflex.dbo.st.familia='MP208'
wich returns:
st_tabatphc stamp cod_art ref
------------------------- ------------------------- ------------------ ------------------
PPA2KQ10663X3691050277866 PPA2KQ10663X3691050277866 0005023 0005023
PPA1PQ0L5I421262263298320 PPA1PQ0L5I421262263298320 0061 0061
PUS2JX0PKVZE1845946971080 PUS2JX0PKVZE1845946971080 01260 01260
PUS2JX0ORYDL1731810264135 PUS2JX0ORYDL1731810264135 01277 01277
PUS2JX0OMEKV1710323326516 PUS2JX0OMEKV1710323326516 104031 104031
PUS2K01085133702171401835 PUS2K01085133702171401835 11302 11302
PUS2JX0LJHI01309067655480 PUS2JX0LJHI01309067655480 114139 114139
ok, this are de ones i want to update,
so my question is how can i update a field in TABATPCO where the condition from my query A is true
thanks
Sorry, but still as clear as mud to me. Please read the article I recommended and follow the quidelines in that article to post your tables DDL (create statements), sample data (INSERT statements), expected results based on sample data.
One of the problems I have with the above is how the tables are joined. It appears the your join criteria is using columns from the same table, tabat.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply